Quick Reference Study Notes for Structured Query Language (SQL) (Advanced)

Structured Query Language

Structured Query Language (SQL) is a standard computer language for relational database management and data manipulation. SQL is used to query, insert, update and modify data.

Components of SQL

SQL code is divided into four main categories:

DDL (Data Definition Language)

Set of SQL commands used to create, modify and delete database structures but not data. Examples are: CREATE, ALTER, TRUNCATE, DROP.

DML (Data Manipulation Language)

Area of SQL that allows changing data within the database. DML is used to add, update, delete data. Examples are INSERT, UPDATE, DELETE

DCL (Data Control Language)

Set of commands that control access to data and database. DCL is used to assign and revoke database rights and permissions. Example statements are GRANT and REVOKE.

DQL (Data Query Language)

It is the component of SQL that allows getting data from the database and imposing ordering upon it. It is the heart of the SQL statements set. Example is SELECT statement.

 

Basic keywords

Let’s take a look at some of the keywords that you’ll come across:

Keyword

Explanation

SELECT

Retrieve data from a database.

FROM

Declares which table/view to select or delete from

WHERE

Filters a result set to include only records that fulfill a specified condition

=

Used for comparing a value to a specified input

LIKE

Special operator used with the WHERE clause to search for a specific pattern in a column

GROUP BY

Groups the result set (used with aggregate functions: COUNT, MAX, MIN, SUM, AVG)

HAVING

Specifies that only rows where aggregate values meet the specified conditions should be returned. Used because the WHERE keyword cannot be used with aggregate functions

INNER JOIN

Returns all rows where key record of one table is equal to key records of another

LEFT JOIN 

Returns all rows from the ‘left’ (1st) table with the matching rows in the right (2nd)

RIGHT  JOIN           

Returns all rows from the ‘right’ (2nd) table with the matching rows in the left (1st)

FULL OUTER JOIN           

Returns rows that match either in the left or right table

 

Aggregate Functions

An aggregate function is a function where the values of multiples rows are grouped to form a single value.

Function

Explanation

COUNT

Return the number of rows in a certain table/view

SUM

To return the total sum of the values in a column

AVG

Returns the average for a group of values

MIN

Returns the smallest value of the group

MAX

Returns the largest value of the group

 

DQL Statements

Statement

Explanation

Example

SELECT * FROM tablename

Select all rows and columns from table

SELECT * FROM employees

SELECT c1, c2 FROM tablename

SELECT data from columns c1 and c2 from table

SELECT name, joining_date FROM employees

SELECT * FROM tablename WHERE condition

Select data for all columns from a table named tablename with a condition

SELECT * FROM employees WHERE name = ‘Amit’

SELECT c1, c2 FROM tablename WHERE condition

Select data from columns c1 and c2 from table with a condition.

SELECT id, name, joining_date FROM employees WHERE salary > 10000 ;

SELECT DISTINCT c1 FROM tablename WHERE condition

Select distinct rows from table named tablename for all columns or specified column(s) with or without condition

SELECT DISTINCT salary FROM employees

SELECT c1, c2 FROM tablename WHERE condition ORDER BY c1 [ASC | DESC]

Select rows from table with or without condition in ascending or descending order of column c1

SELECT * FROM employees ORDER BY salary DESC  

SELECT c1, c2 FROM tablename WHERE condition LIMIT n OFFSET offset

Skip offset of rows and return the next n rows

SELECT * FROM employees LIMIT 10 OFFSET 5

SELECT aggregate(c1), c2 FROM tablename GROUP BY c2;

Query data from table to aggregate the data based on a column.

SELECT MAX(salary), department_id FROM employees GROUP BY department_id

SELECT aggregate(c1), c2 FROM tablename GROUP BY c2 HAVING condition

Query data from table to aggregate the data based on a column and a condition.

SELECT count(*) AS number_of_employees_in_dept, department_id FROM employees GROUP BY department_id HAVING number_of_employees_in_dept > 10

SELECT c1, c2 FROM table1 INNER JOIN table2 ON condition

Query data from multiple tables and inner join the result based on a condition and display a single result

SELECT emp_name, dept_name FROM employees INNER JOIN departments ON employees.dept_id = departments.id;

SELECT c1, c2 FROM table1 LEFT JOIN table2 ON condition

Query data from multiple tables and left join the result based on a condition and display as a single result

SELECT emp_name, dept_name FROM employees LEFT JOIN departments ON employees.dept_id = departments.id;

SELECT c1, c2 FROM table1 RIGHT JOIN table2 ON condition

Query data from multiple tables and right join the result based on a condition and display as a single result

SELECT dept_name, emp_name FROM employees LEFT JOIN departments ON employees.dept_id = departments.id;

SELECT c1, c2

FROM table1

FULL OUTER JOIN table2 on condition

Query data from multiple tables and full join the result based on a condition and display as a single result

SELECT dept_name, emp_name FROM employees FULL OUTER JOIN departments ON employees.dept_id = departments.id;

SELECT c1, c2

FROM table1

CROSS JOIN table2

Query data from multiple tables and cross join the result to obtain a Cartesian product of the records and display as a single result

 

SELECT c1, c2

FROM table1, table2

Same as above Cartesian Product

 

SELECT c1, c2

FROM table1 UNION [ALL] SELECT  c1, c2 FROM table2

Select column(s) from table1  and column(s) from table 2 and combine the rows from these two queries.

 

SELECT c1, c2

FROM table1 INTERSECT SELECT  c1, c2 FROM table2

Select column(s) from table1  and column(s) from table 2 and return the intersection of these two queries.

 

SELECT c1 FROM tablename

WHERE c1 [NOT] LIKE pattern

Select column c1 from a table named tablename and query the rows using pattern matching %

SELECT * FROM employees WHERE emp_name LIKE ‘%yog%’

SELECT c1 FROM tablename

WHERE c1 [NOT] IN test_list

Select column c1 from a table name tablename and return the rows that are (or are not) in test_list

SELECT* FROM employees WHERE dept_id IN (2,4,6)

SELECT c1, c2 FROM tablename WHERE c2 BETWEEN value1 AND value2

BETWEEN operator is used to filter the result set within the specified range. Here, records are fetched from table named tablename where column c2 have values between value1 and value2

SELECT * FROM employees WHERE salary BETWEEN 10000 AND 20000

SELECT c1 FROM tablename

WHERE c1 IS [NOT] NULL

Select column c1 from table and check if values are null or not.

SELECT emp_name, salary, dept_id FROM employees WHERE pan_number IS NULL

 

DDL Statements

Statement

Explanation

Example

CREATE TABLE table_name (col_name1 DATA_TYPE (Size) [CONSTRAINT], col_name1 DATA_TYPE (Size) [CONSTRAINT], .., CONSTRAINT)  

This statement creates a new table in database with new columns and column definition separated by comma.

CREATE TABLE employees (id INT PRIMARY KEY,

name varchar(30), dept_id INT, salary INT, address varchar(100), CONSTRAINT foreign_dept_id FOREIGN KEY (dept_id) REFERENCES departments (id)

ALTER TABLE table_name ADD column_name DATA_TYPE

Adds a new column to the table

ALTER table employees ADD city varchar(20)

ALTER TABLE table_name DROP COLUMN column_name

Drops a column from table

ALTER TABLE employees DROP COLUMN pan_number

ALTER TABLE table_name [ALTER | MODIFY] [COLUMN] column_name DATA_TYPE

Changes the data type or size of datatype of a column. SQL Server and MS Access uses statement “ALTER COLUMN column_name DATA_TYPE”; MS SQL and prior versions of Oracle 10g uses “MODIFY COLUMN column_name DATA_TYPE”; Oracle 10g and later uses “MODIFY column_name DATA_TYPE”.

ALTER TABLE employees MODIFY city varchar(30)

DROP TABLE table_name

Deletes entire table from database.

 

 

DML Statements

Statement

Explanation

Example

INSERT INTO table_name (column_1, column_2, column_3)
VALUES (value_1, 'value_2', value_3);

Inserts a new row into a table named table_name.

INSERT INTO employees (id, name, dept_id, salary) VALUES (1, ‘bob’, 4, 20000)

INSERT INTO table_name (column_1, column_2, column_3)
VALUES (value_1, 'value_2', value_3), (value_1, ‘value_2’, value_3);

Inserts multiple rows into a table named table_name

INSERT INTO employees (id, name, dept_id, salary) VALUES (1, ‘bob’, 4, 20000), (2, ‘john’, 3, 15000)

INSERT INTO table1 (column_list) SELECT column_list FROM table2

Inserts rows from table2 into table named table1

INSERT INTO employees (name, dept_id) SELECT (emp_name, dept_id FROM temp_emp)

UPDATE table_name
SET some_column1 = some_value1

Modifies the existing record of a values of specified column of all rows

UPDATE employees SET emloyee_status = “full_time”

UPDATE table_name
SET some_column1 = some_value1, some_column2 =  some_value2
WHERE some_column2 = some_value2;

Modifies the existing record of a values of specified column for  rows that match the condition

UPDATE employees SET salary = salary + (0.20 * salary) WHERE dept_id = 4

DELETE FROM table_name

Deletes all rows from table

DELETE FROM employees

DELETE FROM table_name WHERE condition

Deletes rows that match the condition

DELETE FROM employees WHERE salary < 10000

 

DCL Statements

Statement

Explanation

Example

GRANT <object_privileges> ON <object_name> TO <user> [GRANT OPTION]

To grant access to a particular user based on the grant option and access requirement. Grant option allows the grantee to in turn grant privileges to other users.

GRANT SELECT, UPDATE  ON employees TO Shivam WITH GRANT OPTION

REVOKE <object_privileges> ON <object_name> FROM <user>

To revoke access to a particular user from a particular object

REVOKE UPDATE ON employees FROM Shivam


*NOTE : "This study material is collected from multiple sources to make a quick refresh course available to students."

This website uses cookies to improve user experience. By using our website you consent to all cookies in accordance with our Cookie Policy. More info. I Agree