PO SQL Commands | SQL Course

Back to SQL Course

SQL commands are segregated into the following types

DDL – Data Definition Language
DML – Data Manipulation Language
DQL – Data Query Language
DCL – Data Control Language
TCL – Transaction Control Language

What are the different DDL commands in SQL?

DDL commands are used to define or alter the structure of the database.

CREATE: To create databases and database objects
ALTER: To alter existing database objects
DROP: To drop databases and databases objects
TRUNCATE: To remove all records from a table but not its database structure
RENAME: To rename database objects

What are the different DML commands in SQL?

DML commands are used for managing data present in the database.

SELECT: To select specific data from a database
INSERT: To insert new records into a table
UPDATE: To update existing records
DELETE: To delete existing records from a table

What are the different DCL commands in SQL?

DCL commands are used to create roles, grant permission and control access to the database objects.

GRANT: To provide user access
DENY: To deny permissions to users
REVOKE: To remove user access

What are the different TCL commands in SQL?

TCL commands are used to manage the changes made by DML statements.

COMMIT: To write and store the changes to the database
ROLLBACK: To restore the database since the last commit

What is the need of MERGE statement?

This statement allows conditional update or insertion of data into a table. It performs an UPDATE if a row exists, or an INSERT if the row does not exist.

Let's see some basic Queries

Creating a table and inserting data

SQL: CREATE table with multiple data types

CREATE TABLE friends (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    weight REAL);

INSERT INTO friends VALUES (1, "Praveen", 34, 80);

SQL: CREATE table with a primary key

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    phone TEXT);
INSERT INTO customers VALUES (1, "555-222-3333");

SQL: CREATE table with a primary key and autoincrement

CREATE TABLE customers_orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER,
    item TEXT);
/* It will automatically pick an id that's different from other ones. */
INSERT INTO customers_orders (customer_id, item) VALUES (1, "Hot Air Balloon");

SQL: Specifying DEFAULT values in CREATE TABLE

CREATE TABLE courses
    (id INTEGER PRIMARY KEY,
    name TEXT,
    prereqs TEXT DEFAULT "None",
    num_hours INTEGER DEFAULT 15);    
INSERT INTO courses (name, num_hours) VALUES
    ("Intro to JS", 25);

SQL: CREATE TABLE with foreign key reference


CREATE TABLE artists (
  id    INTEGER PRIMARY KEY, 
  name  TEXT
);
CREATE TABLE tracks (
  traid     INTEGER, 
  title   TEXT, 
  artist INTEGER,
  FOREIGN KEY(artist) REFERENCES artists(id)
);
INSERT INTO artists VALUES (1, "Tom Chapin");
INSERT INTO tracks VALUES (1, "Great Big Words", 1);

SQL: INSERTing values in tables

CREATE TABLE us_states (
    id INTEGER PRIMARY KEY,
    name TEXT,
    flower TEXT);
INSERT INTO us_states VALUES (1, "California", "California Poppy");
/* Or you can only specify some columns */
INSERT INTO us_states (name, flower) VALUES ("Alaska", "Forget-me-not");

SQL: SELECTing with WHERE conditions

CREATE TABLE fruits (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price INTEGER,
    season TEXT);
/* What are the cheap fruits? */
SELECT * FROM fruits WHERE price < 4;
/* What are the expensive fruits? */
SELECT * FROM fruits WHERE price > 5;
/* What are the cheap fruits in the summer? */
SELECT * FROM fruits WHERE price < 4 AND season = "summer";
/* What fruits can I get in fall or winter? */
SELECT * FROM fruits WHERE season = "summer" OR season = "winter";

SQL: SELECTing with WHERE conditions

CREATE TABLE fruits (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price INTEGER,
    season TEXT);
/* What are the cheap fruits? */
SELECT * FROM fruits WHERE price < 4;
/* What are the expensive fruits? */
SELECT * FROM fruits WHERE price > 5;
/* What are the cheap fruits in the summer? */
SELECT * FROM fruits WHERE price < 4 AND season = "summer";
/* What fruits can I get in fall or winter? */
SELECT * FROM fruits WHERE season = "summer" OR season = "winter";

SQL: Filter with IN


CREATE TABLE shoes (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL,
    type TEXT);
/* Which shoes will make me taller? */
SELECT * FROM shoes WHERE type IN ("heel", "wedge");
/* This is equivalent to: */
SELECT * FROM shoes WHERE type = "heel" OR type = "wedge";
/* Which shoes *won't* make me taller? */
SELECT * FROM shoes WHERE type NOT IN ("heel", "wedge");

SQL: SELECTing rows

CREATE TABLE veggies (
    id INTEGER PRIMARY KEY,
    name TEXT,
    season TEXT);
/* Which of your vegetables should you eat? */
SELECT * FROM veggies;
/* Just show the names and seasons, we don't need to see ids */
SELECT name, season FROM veggies;

SQL: SELECT with ORDER BY

CREATE TABLE skyscrapers (id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT,
    height_meters INTEGER,
    floors INTEGER
);
/* Sort by their height in meters */
SELECT * FROM skyscrapers ORDER BY height_meters;
/* Reverse that sort (highest to lowest) */
SELECT * FROM skyscrapers ORDER BY height_meters DESC;
/* Sort by their floors, lowest to highest */
SELECT * FROM skyscrapers ORDER BY floors ASC;

SQL: Transform SELECT results with CASE

CREATE table species (
    id INTEGER PRIMARY KEY,
    name TEXT,
    appeared INTEGER /* mya, millions of years ago */,
    phylum TEXT
    );
SELECT name, CASE 
    WHEN appeared < 540 AND appeared >= 245 THEN "Paleozoic"
    WHEN appeared < 245 AND appeared >= 65 THEN "Mesozoic"
    WHEN appeared < 65 THEN "Cenozoic"
    END "era"
    FROM species
    ORDER BY era;

SQL: Filtering with LIKE

CREATE TABLE dresses (
    id INTEGER PRIMARY KEY,
    name TEXT,
    description TEXT,
    price REAL);
/* Which ones have floral patterns? */
SELECT  * FROM dresses WHERE description LIKE "%floral%";
/* Which ones are blue? */
SELECT  * FROM dresses WHERE description LIKE "%blue%";

SQL: Using SELECT with LIMIT


CREATE TABLE top_programs(id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    creator TEXT,
    votes INTEGER);
/* But let's just show the top 3! */
SELECT * FROM top_programs ORDER BY votes DESC LIMIT 3;

SQL: ROUND and other core functions

CREATE TABLE organs (id INTEGER PRIMARY KEY,
    name TEXT,
    weight INTEGER,
    important_functions TEXT);
SELECT LOWER(name) AS name,
    ROUND(weight) AS rounded_weight,
    LOWER(TRIM(important_functions, ".")) AS desc
    FROM organs WHERE weight > 1;

SQL: SELECT with aggregate functions

CREATE TABLE paintings
    (id INTEGER PRIMARY KEY,
    name TEXT,
    artist TEXT,
    year INTEGER,
    price REAL);
/* What's the most expensive price for a painting? */
SELECT MAX(price) as most_dollars_paid
    FROM paintings;
/* What's the average year these were painted? */
SELECT AVG(year) 
    FROM paintings;
/* How much money was paid for these paintings total? */
SELECT SUM(price) AS total_dollars_paid
    FROM paintings;
/* How many cost more than 2 million? */
SELECT COUNT(*) AS greater_than_2mil
    FROM paintings WHERE price > 200000000;
/* How many unique years are there? */
SELECT COUNT(DISTINCT year) AS years_represented
    FROM paintings;

SQL: Grouping SELECT results with GROUP BY


CREATE TABLE bridges (
    id INTEGER PRIMARY KEY,
    name TEXT,
    length INTEGER, /* meters */
    country TEXT);
SELECT country, COUNT(*)
    FROM bridges GROUP BY country;  
SELECT country, SUM(length)
    FROM bridges GROUP BY country;

SQL: Using HAVING with GROUPED BY

CREATE TABLE nobel_prizes (
    id INTEGER PRIMARY KEY,
    name TEXT,
    field TEXT,
    country TEXT,
    year INTEGER
    );
/* Which countries had more than 1 person win? */
SELECT country, COUNT(*) as num_won FROM nobel_prizes
    GROUP BY country 
    HAVING num_won > 1;

SQL: JOIN on tables

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT,
    teacher_id INTEGER);    
CREATE TABLE teachers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT);
/* Show students next to their teachers */
SELECT students.name, teachers.name AS teacher_name
    FROM students
    JOIN teachers
    ON students.teacher_id = teachers.id;
/* Show students next to their teachers,
   even if they don't have a teacher assigned
*/ 
SELECT students.name, teachers.name AS teacher_name
    FROM students
    LEFT OUTER JOIN teachers
    ON students.teacher_id = teachers.id;

SQL: UPDATE and DELETE

CREATE TABLE inventory (
    id INTEGER PRIMARY KEY,
    item TEXT,
    price REAL);
/* It's sale time for Halloween merchandise! */
UPDATE inventory SET price = 20.00 WHERE id = 4;
SELECT * FROM inventory;
/* Woo, it got sold! */
DELETE FROM inventory WHERE id = 4;
SELECT * FROM inventory;

More complex queries with AND/OR

CREATE TABLE exercise_logs
    (id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT,
    minutes INTEGER, 
    calories INTEGER,
    heart_rate INTEGER);
SELECT * FROM exercise_logs WHERE calories > 50 ORDER BY calories;
/* AND */
SELECT * FROM exercise_logs WHERE calories > 50 AND minutes < 30;
/* OR */
SELECT * FROM exercise_logs WHERE calories > 50 OR heart_rate > 100;

Querying IN subqueries

CREATE TABLE exercise_logs
    (id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT,
    minutes INTEGER, 
    calories INTEGER,
    heart_rate INTEGER);
SELECT * FROM exercise_logs WHERE type = "biking" OR type = "hiking" OR type = "tree climbing" OR type = "rowing";
/* IN */
SELECT * FROM exercise_logs WHERE type IN ("biking", "hiking", "tree climbing", "rowing");
CREATE TABLE drs_favorites
    (id INTEGER PRIMARY KEY,
    type TEXT,
    reason TEXT);
SELECT type FROM drs_favorites;
SELECT * FROM exercise_logs WHERE type IN (
    SELECT type FROM drs_favorites);    
SELECT * FROM exercise_logs WHERE type IN (
    SELECT type FROM drs_favorites WHERE reason = "Increases cardiovascular health");    
/* LIKE */
SELECT * FROM exercise_logs WHERE type IN (
    SELECT type FROM drs_favorites WHERE reason LIKE "%cardiovascular%");

Restricting grouped results with HAVING

CREATE TABLE exercise_logs
    (id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT,
    minutes INTEGER, 
    calories INTEGER,
    heart_rate INTEGER);
SELECT * FROM exercise_logs;
SELECT type, SUM(calories) AS total_calories FROM exercise_logs GROUP BY type;
SELECT type, SUM(calories) AS total_calories FROM exercise_logs
    GROUP BY type
    HAVING total_calories > 150;
SELECT type, AVG(calories) AS avg_calories FROM exercise_logs
    GROUP BY type
    HAVING avg_calories > 70;     
SELECT type FROM exercise_logs GROUP BY type HAVING COUNT(*) >= 2;

Calculating results with CASE

CREATE TABLE exercise_logs
    (id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT,
    minutes INTEGER, 
    calories INTEGER,
    heart_rate INTEGER);
SELECT * FROM exercise_logs;
SELECT COUNT(*) FROM exercise_logs WHERE heart_rate > 220 - 30;
/* 50-90% of max*/
SELECT COUNT(*) FROM exercise_logs WHERE
    heart_rate >= ROUND(0.50 * (220-30)) 
    AND  heart_rate <= ROUND(0.90 * (220-30)); 
/* CASE */
SELECT type, heart_rate,
    CASE 
        WHEN heart_rate > 220-30 THEN "above max"
        WHEN heart_rate > ROUND(0.90 * (220-30)) THEN "above target"
        WHEN heart_rate > ROUND(0.50 * (220-30)) THEN "within target"
        ELSE "below target"
    END as "hr_zone"
FROM exercise_logs;
SELECT COUNT(*),
    CASE 
        WHEN heart_rate > 220-30 THEN "above max"
        WHEN heart_rate > ROUND(0.90 * (220-30)) THEN "above target"
        WHEN heart_rate > ROUND(0.50 * (220-30)) THEN "within target"
        ELSE "below target"
    END as "hr_zone"
FROM exercise_logs
GROUP BY hr_zone;

Difference between WHERE and HAVING clause

WHERE clause can be used with a Select, Update and Delete Statement Clause but the HAVING clause can be used only with a Select statement.
We can't use an aggregate functions in the WHERE clause unless it is in a sub-query contained in a HAVING clause whereas we can use an aggregate function in the HAVING clause. We can use a column name in the HAVING clause but the column must be contained in the group by clause.
WHERE is used before the GROUP BY clause whereas a HAVING clause is used to impose a condition on the GROUP Function and is used after the GROUP BY clause in the query.
A WHERE clause applies to each and every row whereas a HAVING clause applies to summarized rows (summarized with GROUP BY).
In the WHERE clause the data that is fetched from memory depending on a condition whereas in HAVING the completed data is first fetched and then separated depending on the condition.

Mathematical functions in MySQL

COUNT()

SELECT COUNT(salary)
FROM employees
WHERE salary >= 40000;

AVG()

SELECT AVG(salary)
FROM employees;

SUM()

SELECT SUM(Price)
FROM Products;

MIN()

SELECT MIN(column_name)
FROM table_name;

MAX()

SELECT MAX(column_name)
FROM table_name;

What is the order of SQL SELECT?

Order of SQL SELECT statement is as follows SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

What is a Composite PRIMARY KEY?

Composite PRIMARY KEY is a primary key created on more than one column (combination of multiple fields) in a table.

What do you mean by foreign key?

A Foreign key is a field which can uniquely identify each row in another table. And this constraint is used to specify a field as Foreign key. That is, this field points to primary key of another table. This usually creates a kind of link between the two tables.

CREATE TABLE Orders
(
O_ID int NOT NULL,
ORDER_NO int NOT NULL,
C_ID int,
PRIMARY KEY (O_ID),
FOREIGN KEY (C_ID) REFERENCES Customers(C_ID)
)

What is a primary key?

A primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. If a row is deleted from the table, its primary key may not be assigned to any new rows in the future. To define a field as primary key, following conditions had to be met :

No two rows can have the same primary key value.
Every row must have a primary key value
Primary key field cannot be null
Values in primary key columns can never be modified or updated

What is a Composite Key ?

A Composite primary key is a type of candidate key, which represents a set of columns whose values uniquely identify every row in a table.

For example - if "Employee_ID" and "Employee Name" in a table is combined to uniquely identifies a row its called a Composite Key.

What is a Composite Primary Key ?

A Composite primary key is a set of columns whose values uniquely identify every row in a table. What it means is that, table which contains composite primary key will be indexed based on columns specified in the primary key. This key will be referred in Foreign Key tables.

For example - if combined effect of columns, "Employee_ID" and "Employee Name" in a table is required to uniquely identifies a row, its called a Composite Primary Key. In this case, both the columns will be represented as primary key.

What is a Foreign Key ?

When a "one" table's primary key field is added to a related "many" table in order to create the common field which relates the two tables, it is called a foreign key in the "many" table.

For example, salary of an employee is stored in salary table. Relation is established via foreign key column "Employee_ID_Ref" which refers "Employee_ID" field in Employee table

What is a Unique Key ?

Unique key is same as primary with difference being existence of null. Unique key field allows one value as NULL value.

What is the difference between Primary Key and Unique Key?

We can have only one Primary Key in a table whereas we can have more than one Unique Key in a table.
The Primary Key cannot have a NULL value whereas a Unique Key may have only one null value.
By default, a Primary Key is a Clustered Index whereas by default, a Unique Key is a unique non-clustered index.
A Primary Key supports an Auto Increment value whereas a Unique Key doesn't support an Auto Increment value.

What is the difference between primary key and unique constraints?

Primary key cannot have NULL value, the unique constraints can have NULL values.

There is only one primary key in a table, but there can be multiple unique constrains.

What is a primary key, a foreign key and unique key?

Primary key is a field in the table which uniquely identifies a row. It cannot be NULL

Foreign key is a field in one table which is a primary key in another table. A relationship is created between the two tables by referencing the foreign key of one table with the primary key of another table.

Unique Key uniquely identifies a record in a table. There can be many unique key constraints defined on a table.

What is the difference between BETWEEN and IN condition operators?

The BETWEEN operator is used to display rows based on a range of values. The values can be numbers, text, and dates as well. BETWEEN operator gives us the count of all the values occurs between a particular range.

The IN condition operator is used to check for values contained in a specific set of values. IN operator is used when we have more than one value to choose.

Which are the different character-manipulation functions in SQL?

CONCAT: join two or more values together.
SUBSTR: used to extract the string of specific length.
LENGTH: return the length of the string in numerical value.
INSTR: find the exact numeric position of a specified character.
LPAD: padding of the left-side character value for right-justified value.
RPAD: padding of right-side character value for left-justified value.
TRIM: remove all the defined character from the beginning, end or both beginning and end.
REPLACE: replace a specific sequence of character with other sequences of character.

What is the difference between TRUNCATE and DELETE commands?

The DELETE command is used to remove rows from a table based on a WHERE condition whereas TRUNCATE removes all rows from a table.
So we can use a where clause with DELETE to filter and delete specific records whereas we cannot use a Where clause with TRUNCATE.
DELETE is executed using a row lock, each row in the table is locked for deletion whereas TRUNCATE is executed using a table lock and the entire table is locked for removal of all records.
DELETE is a DML command whereas TRUNCATE is a DDL command.
DELETE retains the identity of the column value whereas in TRUNCATE, the Identify column is reset to its seed value if the table contains any identity column.
To use Delete you need DELETE permission on the table whereas to use Truncate on a table you need at least ALTER permission on the table.
DELETE uses more transaction space than the TRUNCATE statement whereas Truncate uses less transaction space than DELETE statement.
DELETE can be used with indexed views whereas TRUNCATE cannot be used with indexed views.
The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row whereas TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
Delete activates a trigger because the operation is logged individually whereas TRUNCATE TABLE can't activate a trigger because the operation does not log individual row deletions.