SQL Queries

This pages illustrates details associated with Create, Update and Delete structured and relation data.

SQL commands

These are essential for managing and manipulating databases effectively. They are categorized into two main types, each serving specific purposes in database operations. There are other three minor types that are usually managed by the database administrator.

1. Data Definition Language (DDL):

   • DDL commands are used to define and modify the structure of database objects such as tables, indexes, and schemas. Changes made through DDL commands are automatically committed to the database.

   • Common DDL Commands:

     • CREATE: Creates new database objects (e.g., tables, databases).

       CREATE TABLE Employees (ID INT, Name VARCHAR(255));

     • ALTER: Modifies existing database objects.

       ALTER TABLE Employees ADD COLUMN Address VARCHAR(255);

     • DROP: Deletes existing database objects.

       DROP TABLE Employees;

     • TRUNCATE: Removes all records from a table but retains the table structure.

       TRUNCATE TABLE Employees;

2. Data Manipulation Language (DML):

   • DML commands are used for manipulating data within existing tables. This includes inserting, updating, and deleting records.

   • Common DML Commands:

     • INSERT: Adds new records to a table.

       INSERT INTO Employees (ID, Name) VALUES (1, 'John Doe');

     • UPDATE: Modifies existing records.

       UPDATE Employees SET Name = 'Jane Doe' WHERE ID = 1;

     • DELETE: Removes records from a table.

       DELETE FROM Employees WHERE ID = 1;

3. Data Query Language (DQL):

   • DQL is primarily concerned with querying data from the database. The main command in this category is SELECT.

   • Common DQL Command:

     • SELECT: Retrieves data from one or more tables.

       SELECT Name FROM Employees WHERE ID = 1;

4. Data Control Language (DCL):

   • DCL commands manage user permissions and access controls within the database system.

   • Common DCL Commands:

     • GRANT: Assigns privileges to users.

       GRANT SELECT ON Employees TO UserName;

     • REVOKE: Removes previously granted privileges.

       REVOKE SELECT ON Employees FROM UserName;

5. Transaction Control Language (TCL):

   • TCL commands manage transactions within the database, ensuring that a series of operations are completed successfully or rolled back if an error occurs.

   • Common TCL Commands:

     • COMMIT: Saves all changes made in the current transaction.

     • ROLLBACK: Undoes changes made in the current transaction if an error occurs.

These SQL command types collectively enable users to create, manipulate, query, and control access to data in relational databases, forming the backbone of effective database management systems.

Commands in Detail

SELECT

The SQL SELECT statement is a powerful tool used to retrieve data from databases. It can be utilized in various forms, each serving different purposes. Here are the main types of SELECT statements:

1. Basic SELECT Statement:

   • Retrieves specific columns from a table.

   SELECT column1, column2 FROM table_name;

2. SELECT All Columns:

   • Uses an asterisk (*) to select all columns from a table.

   SELECT * FROM table_name;

3. SELECT with WHERE Clause:

   • Filters records based on specified conditions.

   SELECT column1, column2 FROM table_name WHERE condition;

4. SELECT with ORDER BY Clause:

   • Sorts the result set by one or more columns.

   SELECT column1, column2 FROM table_name ORDER BY column1 ASC|DESC;

5. SELECT with GROUP BY Clause:

   • Groups rows that have the same values in specified columns into summary rows.

   SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

6. SELECT with HAVING Clause:

   • Filters groups created by GROUP BY based on specified conditions.

   SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > value;

7. SELECT with JOINs:

   • Combines rows from two or more tables based on related columns.

   • INNER JOIN: Returns records with matching values in both tables.

     SELECT a.column1, b.column2 FROM table_a a 
     INNER JOIN table_b b ON a.common_column = b.common_column;

   • LEFT JOIN: Returns all records from the left table and matched records from the right table.

     SELECT a.column1, b.column2 FROM table_a a 
     LEFT JOIN table_b b ON a.common_column = b.common_column;

8. SELECT with DISTINCT:

   • Returns unique values from a specified column.

   SELECT DISTINCT column1 FROM table_name;

9. SELECT with Subqueries:

   • A nested query that retrieves data based on results from another query.

   SELECT column1 FROM table_name 
   WHERE column2 IN (SELECT column2 FROM another_table WHERE condition);

10. SELECT INTO Statement:

    • Copies data from one table into another new table.

    SELECT * INTO new_table FROM existing_table WHERE condition;

INSERT

The SQL INSERT statement is essential for adding new records to a database table. There are several types of INSERT statements, each serving different purposes. Here’s a comprehensive overview:

1. Single Row Insert:

   • This is the most basic form of the INSERT statement, used to add a single row to a table.

   • Syntax:

     INSERT INTO table_name (column1, column2, column3) 
     VALUES (value1, value2, value3);

2. Insert Without Specifying Columns:

   • You can insert data without specifying the column names if you provide values for all columns in the correct order.

   • Syntax:

     sqlINSERT INTO table_name VALUES (value1, value2, value3);

3. Multi-Row Insert:

   • This allows you to insert multiple rows in a single INSERT statement, which can improve performance.

   • Syntax:

     INSERT INTO table_name (column1, column2) VALUES 
     (value1a, value2a), 
     (value1b, value2b), 
     (value1c, value2c);

4. Insert Using SELECT Statement:

   • You can insert data into a table by selecting it from another table. This is useful for copying data.

   • Syntax:

     INSERT INTO target_table (column1, column2) 
     SELECT column1, column2 FROM source_table WHERE condition;

5. Insert with Default Values:

   • If you want to insert a row and use default values for certain columns, you can specify DEFAULT for those columns.

   • Syntax:

     INSERT INTO table_name (column1, column2) VALUES (value1, DEFAULT);

6. Insert with ON DUPLICATE KEY UPDATE (MySQL specific):

   • This allows you to update existing records if a duplicate key violation occurs during the insert operation.

   • Syntax:

     INSERT INTO table_name (column1, column2) VALUES (value1, value2)
     ON DUPLICATE KEY UPDATE column2 = value2;

7. Insert with RETURNING Clause (PostgreSQL specific):

   • This allows you to return values from the inserted row(s), which is useful for obtaining auto-generated keys.

   • Syntax:

     INSERT INTO table_name (column1) VALUES (value1) RETURNING column_id;

CREATE

The CREATE statement in SQL is primarily used to create database objects such as tables, indexes, views, and types. Here’s a comprehensive overview of the various types of CREATE statements:

1. CREATE TABLE:

   • This statement is used to create a new table in the database. You define the table name, columns, and their data types.

   • Syntax:

     sqlCREATE TABLE table_name (
         column1 datatype,
         column2 datatype,
         ...
     );

   • Example:

     sqlCREATE TABLE Employees (
         EmployeeID INT PRIMARY KEY,
         FirstName VARCHAR(50),
         LastName VARCHAR(50),
         HireDate DATE
     );

2. CREATE TABLE AS:

   • This creates a new table based on the result of a SELECT query from an existing table.

   • Syntax:

     sqlCREATE TABLE new_table_name AS
     SELECT column1, column2 FROM existing_table_name WHERE condition;

   • Example:

     sqlCREATE TABLE NewEmployees AS
     SELECT * FROM Employees WHERE HireDate > '2023-01-01';

ALTER

The ALTER statement in SQL is used to modify the structure of an existing database object, primarily tables. Here are the various types of ALTER statements:

1. ALTER TABLE - ADD Column:

   • This command adds one or more new columns to an existing table.

   • Syntax:

     ALTER TABLE table_name ADD column_name datatype;

   • Example:

     ALTER TABLE Employees ADD Age INT;

2. ALTER TABLE - DROP Column:

   • This command removes one or more columns from a table.

   • Syntax:

     ALTER TABLE table_name DROP COLUMN column_name;

   • Example:

     ALTER TABLE Employees DROP COLUMN Age;

3. ALTER TABLE - MODIFY/ALTER Column:

   • This command changes the data type or constraints of an existing column.

   • Syntax for MySQL/Oracle:

     ALTER TABLE table_name MODIFY column_name new_datatype;

   • Syntax for SQL Server:

     ALTER TABLE table_name ALTER COLUMN column_name new_datatype;

   • Example:

     ALTER TABLE Employees MODIFY LastName VARCHAR(100);

4. ALTER TABLE - RENAME Column:

   • This command renames an existing column in a table.

   • Syntax (varies by database):

     ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

   • Example (for PostgreSQL):

     ALTER TABLE Employees RENAME COLUMN LastName TO Surname;

5. ALTER TABLE - RENAME Table:

   • This command changes the name of an existing table.

   • Syntax:

     ALTER TABLE old_table_name RENAME TO new_table_name;

   • Example:

     ALTER TABLE Employees RENAME TO StaffMembers;

6. ALTER TABLE - ADD CONSTRAINT:

   • This command adds a new constraint (e.g., primary key, foreign key) to an existing table.

   • Syntax:

     ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_type (column_name);

   • Example:

     ALTER TABLE Employees ADD CONSTRAINT pk_employee PRIMARY KEY (EmployeeID);

7. ALTER TABLE - DROP CONSTRAINT:

   • This command removes an existing constraint from a table.

   • Syntax:

     ALTER TABLE table_name DROP CONSTRAINT constraint_name;

   • Example:

     ALTER TABLE Employees DROP CONSTRAINT pk_employee;

These ALTER statements provide flexibility in modifying database structures as requirements evolve, ensuring that databases can adapt to changing needs without requiring complete redesigns.

DROP vs DELETE

In SQL, both the DELETE and DROP commands are used to remove data, but they serve distinct purposes and operate at different levels. Here’s a detailed comparison of the two:

DELETE Command

• Purpose: The DELETE command is used to remove specific rows from a table based on a condition. It allows for selective deletion of data while preserving the table structure.

• Type: It is classified as a Data Manipulation Language (DML) command.

• Syntax:

  DELETE FROM table_name WHERE condition;

• Rollback Capability: Actions performed using DELETE can be rolled back, meaning you can restore deleted rows if needed, as it maintains a transaction log.

• Triggers: The DELETE command can activate triggers defined on the table, allowing for additional actions to occur automatically upon deletion.

• Performance: The performance of DELETE may be slower, especially when removing many rows, as it processes each row individually.

DROP Command

• Purpose: The DROP command is used to completely remove an entire database object, such as a table or database, along with all its data and structure. Once executed, the object cannot be recovered unless a backup exists.

• Type: It is classified as a Data Definition Language (DDL) command.

• Syntax:

  DROP TABLE table_name;

• Rollback Capability: Actions performed using DROP cannot be rolled back; they are permanent and immediate.

• Triggers: The DROP command does not activate any triggers since it removes the entire object from the database.

• Performance: The DROP command is generally faster than DELETE because it does not need to process individual rows; it simply removes the entire structure.

UPDATE

The SQL UPDATE statement is used to modify existing records in a table. It allows for updating single or multiple columns for one or more rows based on specified conditions. Here are the various types of UPDATE statements:

1. Basic UPDATE Statement:

   • This is used to update specific columns in a single row based on a condition.

   • Syntax:

     UPDATE table_name SET column1 = value1 WHERE condition;

   • Example:

     UPDATE Customers SET ContactName = 'Alfred Schmidt' WHERE CustomerID = 1;

2. UPDATE Multiple Columns:

   • You can update multiple columns in a single UPDATE statement.

   • Syntax:

     UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

   • Example:

     UPDATE Customers SET ContactName = 'Alfred Schmidt', City = 'Frankfurt' WHERE CustomerID = 1;

3. UPDATE Multiple Rows:

   • This updates all rows that meet a specified condition.

   • Syntax:

     UPDATE table_name SET column_name = new_value WHERE condition;

   • Example:

     UPDATE Customers SET ContactName = 'Juan' WHERE Country = 'Mexico';

4. UPDATE Without WHERE Clause:

   • If the WHERE clause is omitted, all records in the table will be updated. This should be used with caution.

   • Example:

     UPDATE Customers SET ContactName = 'Juan';

5. UPDATE Using Subquery:

   • You can use a subquery to determine the values to be updated based on data from another table.

   • Syntax:

     UPDATE table_name SET column_name = (SELECT value FROM another_table WHERE condition) WHERE condition;

   • Example:

     UPDATE Customers SET City = (SELECT City FROM Locations WHERE Country = 'Germany') WHERE Country = 'Germany';

6. UPDATE with JOIN (in some SQL dialects):

   • This allows you to update records in one table based on matching records in another table.

   • Syntax (for SQL Server):

     UPDATE a SET a.column1 = b.column2 FROM table_a a 
     JOIN table_b b ON a.common_column = b.common_column WHERE condition;

   • Example:

     UPDATE c SET c.ContactName = b.NewContactName 
     FROM Customers c 
     JOIN NewContacts b ON c.CustomerID = b.CustomerID;

7. Conditional Updates with CASE Statement:

   • You can use a CASE statement within an UPDATE to set different values based on conditions.

   • Syntax:

     UPDATE table_name 
     SET column_name = CASE 
         WHEN condition1 THEN value1 
         WHEN condition2 THEN value2 
         ELSE default_value 
         END 
     WHERE condition;

   • Example:

     UPDATE Customers 
     SET ContactName = CASE 
         WHEN Country = 'Germany' THEN 'Hans Müller' 
         WHEN Country = 'Mexico' THEN 'Juan Pérez' 
         ELSE 'Unknown' 
         END;

These various forms of the UPDATE statement provide flexibility in modifying data within SQL databases, allowing users to perform targeted changes efficiently while maintaining data integrity. Always ensure that conditions are correctly specified to avoid unintended updates, especially when omitting the WHERE clause.

Sample Queries for Review

--Create database
CREATE DATABASE Campus;

--Drop database
DROP DATABASE Campus;

--Create a Table
CREATE TABLE Students (
    StudentID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

--Drop a Table, deletes the entire table
DROP TABLE Students;

--Truncate deletes the data inside a table, but not the table itself.
TRUNCATE TABLE Students;

--Alter to drop a column from existing table
ALTER TABLE Students
DROP COLUMN City;

--Alter the column definition of  a column from existing table
ALTER TABLE Students
MODIFY COLUMN LastName varchar(500);

--Constraints using NOT NULL
CREATE TABLE Students (
    RollNumber int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

--Constraints by altering the table with NOT NULL
ALTER TABLE Persons
MODIFY COLUMN Age int NOT NULL;

--Constraints by using UNIQUE keyword
CREATE TABLE Students (
    RollNumber int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    UNIQUE (ID)
);

--Constraints by using PRIMARY KEY
CREATE TABLE Student (
   ID_Student int NOT NULL  
   RollNumber int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID_Student)
);

--Foreign Key Constraint 

CREATE TABLE Subject (
    SubjectID int NOT NULL,
    SubjectName varchar(255) NOT NULL,
    RollNumber int,
    PRIMARY KEY (SubjectID),
    CONSTRAINT FK_ID_Student FOREIGN KEY (ID_Student)
    REFERENCES Student(ID_Student)
);

--Constraint using CHECK

CREATE TABLE Student (
    ID_Student int NOT NULL,
   RollNumber int NOT NULL,
    FirstName varchar(255) NOT NULL,
    LastName varchar(255),
    Age int,
    CHECK (Age>=16)
);

--Insertion
INSERT INTO Student (ID_Student, RollNumber, FirstName, LastName, Age)
VALUES ('1245', '20162153', 'Sai Anirudh', 'Karre',  '35');

--Selection of data from table
SELECT * FROM Students;


--Filtering the single value data
SELECT * FROM Students WHERE RollNumber='20162153';

--Filtering multi-value data
SELECT * FROM Student WHERE RollNumber in ('20162153','201350856');

--Sort the data based on a column in descending 
SELECT * FROM Student ORDER BY RollNumber DESC;

--Sort the data based on a column in ascending 
SELECT * FROM Student ORDER BY RollNumber ASC;

--Pattern search using filter on a table with multiple condition using AND
SELECT *
FROM Student WHERE FirstName = 'Sai' AND RollNumber LIKE '2016%';

--Pattern search using filter on a table with multiple condition using OR
SELECT *
FROM Student
WHERE FirstName = 'Sai' OR Age = '21';

--Filter using NOT - different variants and wildcard
SELECT * FROM Student WHERE NOT LastName = 'Anirudh';
SELECT * FROM Student WHERE FirstName NOT LIKE 'Sai%';
SELECT * FROM Student WHERE RollNumber NOT BETWEEN 20162153 AND 20162169;
SELECT * FROM Student WHERE RollNumber NOT IN ('20162153', '20162169');
SELECT * FROM Student WHERE  NOT Age > 17;

--Updating data using a Filter
UPDATE Student
SET Age = '23', City= 'Hyderabad'
WHERE RollNumber = 20162153;

--deletion of data for specific filter
DELETE FROM Student WHERE RollNumber='20162153';

--Aggregate function MIN(), MAX(), COUNT(), SUM(), AVG()
SELECT MIN(Marks) FROM Subjects WHERE RollNumber='20162153';
SELECT MAX(Marks) FROM Subjects WHERE RollNumber='20162153';
SELECT SUM(Marks) FROM Subjects WHERE RollNumber='20162153' AND Semester='2';
SELECT AVG(Marks) FROM Subjects WHERE RollNumber='20162153' AND Semester in ('1','2','3');
SELECT COUNT(RollNumber) from Subjects where Marks > 80 and SubjectName='ISS' and Semester=2;

--Group by & Having to be used with Aggregate Function for summary
SELECT COUNT(RollNumber), SubjectName
FROM Subjects
GROUP BY Marks
HAVING COUNT(RollNumber) > 5;

--Column Alias
SELECT RollNumber AS ID FROM Student;

--JOIN - inner
SELECT St.RollNumber, Sb.SubjectName, Sb.Semester
FROM Student as St
INNER JOIN Subjects as Sb ON St.RollNumber=Sb.RollNumber

--JOIN - left
SELECT St.RollNumber, Sb.SubjectName, Sb.Semester
FROM Student as St
LEFT JOIN Subjects as Sb ON St.RollNumber=Sb.RollNumber

SELECT St.RollNumber, Sb.SubjectName, Sb.Semester
FROM Student as St
LEFT OUTER JOIN Subjects as Sb ON St.RollNumber=Sb.RollNumber

--JOIN - left
SELECT St.RollNumber, Sb.SubjectName, Sb.Semester
FROM Student as St
RIGHT JOIN Subjects as Sb ON St.RollNumber=Sb.RollNumber

SELECT St.RollNumber, Sb.SubjectName, Sb.Semester
FROM Student as St
RIGHT OUTER JOIN Subjects as Sb ON St.RollNumber=Sb.RollNumber

--JOIN - full
SELECT St.RollNumber, Sb.SubjectName, Sb.Semester
FROM Student as St
FULL OUTER JOIN Subjects as Sb ON St.RollNumber=Sb.RollNumber