Database Management System

Queries

The SQL commands categorised in 4 categories

1. DDL – Data Definition Language
   • DDL is a set of SQL commands used to create, modify, and delete database structures but not data. These commands are normally not used by a general user, who should be accessing the database via an application.
2. DQl – Data Query Language
   • DQL statements are used for performing queries on the data within schema objects. 
   • SELECT : It is used to retrieve data from the database.
3. DML – Data Manipulation Language
   • The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements.
     1. INSERT : It is used to insert data into a table.
     2. UPDATE: It is used to update existing data within a table.
     3. DELETE : It is used to delete records from a database table.
     4. LOCK: Table control concurrency.
     5. CALL: Call a PL/SQL or JAVA subprogram.
     6. EXPLAIN PLAN: It describes the access path to data.
4. DCL – Data Control Language
   1. DCL includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions, and other controls of the database system. 
   2. List of  DCL commands:
      1. GRANT: This command gives users access privileges to the database.
      2. REVOKE: This command withdraws the user’s access privileges given by using the GRANT command.

DDL- Create and Use Database

-- syntax to create DB
CREATE DATABASE databasename;
-- example
CREATE DATABASE testDB;

-- example to mount|use DB
USE databasename;

-- Example to show databases
SHOW DATABASES;

Create table

-- Creating table
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
); 

-- example 
CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);
-- inserting values into the table
 INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...); 

1. Nth Max record/salary (TODO: Find more efficient way)

SELECT * FROM employee 
WHERE salary= (SELECT DISTINCT(salary) 
FROM employee ORDER BY salary LIMIT n-1,1);
-- Note : here we are getting the exact recordID (you should know the no. of records i.e n)

2. Remove Duplicates

-- using INNER JOIN
DELETE c1 FROM table1 t1
INNER JOIN table2 t2 
WHERE
    t1.id > t2.id AND 
    t1.email = t2.email;
-- Note1: the id column has be primary key
-- Note2: bigger ids are deleted in above and smallest one remains
-- Note2: to delete smaller ids use   t1.id < t2.id and the biggest one remains

3.Find 3 largest marks of students arragned in dec by score first and the n by name in asc