SQL: DDL and basic queries

Why it exists

SQL is the language in which relational databases are used. Its core is a direct, declarative translation of relational algebra and calculus. GATE asks the result of SQL queries on a given instance, the effect of INSERT/UPDATE/DELETE, equivalence of queries, and the meaning of NULL. The statements shown here were executed on a real engine (SQLite); the printed results are its output, not typed by hand.

Prerequisites

You need relational algebra (relational algebra), which gives the meaning of SELECT, FROM and WHERE.

Precise definitions

Sample database (used on all SQL pages): students, courses, enrolments, instructors.

Data definition (DDL). CREATE TABLE name (column type [constraints], ..., table constraints) defines a schema; ALTER TABLE changes it; DROP TABLE removes it. Typical column types: INTEGER, DECIMAL(p, s), CHAR(n), VARCHAR(n), DATE, BOOLEAN. Column or table constraints: NOT NULL, PRIMARY KEY, UNIQUE, DEFAULT value, CHECK (condition), FOREIGN KEY (...) REFERENCES T(...).

Data manipulation (DML). INSERT INTO T [(columns)] VALUES (...) or INSERT INTO T SELECT ...; UPDATE T SET column = expression [WHERE condition]; DELETE FROM T [WHERE condition] (without WHERE it deletes all rows).

Query. SELECT [DISTINCT] expressions FROM tables [WHERE condition] [ORDER BY ...] [LIMIT n]. Meaning in algebra: the FROM clause gives the Cartesian product (or joins) of the tables, WHERE is a selection $\sigma$, the SELECT list is a projection $\pi$ (with renaming and computed columns), DISTINCT removes duplicates, ORDER BY sorts the output. Unlike the algebra, SQL works on bags: duplicates are kept unless DISTINCT (or a set operation) removes them.

How it works

Defining and changing tables

CREATE TABLE Club (cid INTEGER PRIMARY KEY, cname TEXT NOT NULL UNIQUE, founded INTEGER CHECK (founded >= 1900), dept TEXT DEFAULT 'CS');
INSERT INTO Club (cid, cname, founded) VALUES (1, 'Robotics', 2010);
INSERT INTO Club VALUES (2, 'Chess', 1995, 'EE');
SELECT * FROM Club;

cid | cname    | founded | dept
----+----------+---------+-----
1   | Robotics | 2010    | CS
2   | Chess    | 1995    | EE
(2 rows)

The column dept of the first club was not given, so the DEFAULT was used. A statement that violates a constraint is refused as a whole:

-- error
CREATE TABLE Club (cid INTEGER PRIMARY KEY, cname TEXT NOT NULL UNIQUE, founded INTEGER CHECK (founded >= 1900), dept TEXT DEFAULT 'CS');
INSERT INTO Club (cid, cname, founded) VALUES (1, 'Robotics', 1850);

Error: CHECK constraint failed: founded >= 1900

UPDATE and DELETE act on all rows that satisfy the WHERE condition:

UPDATE Student SET age = age + 1 WHERE dept = 'ME';

OK (2 rows affected)

The ME students are Dara (age NULL) and Gita (22). NULL + 1 is NULL, so Dara's age stays NULL; only Gita changes, and the engine counts both rows as affected because both satisfy the condition. A DELETE with no WHERE removes every row:

DELETE FROM Enrolled WHERE grade IS NULL;
SELECT COUNT(*) AS remaining FROM Enrolled;

remaining
---------
13
(1 row)

SELECT, DISTINCT and bags

SELECT dept FROM Student;

dept
----
CS
EE
CS
ME
CS
EE
ME
CS
(8 rows)

Eight rows: SQL keeps duplicates. In relational algebra $\pi_{\text{dept}}(\text{Student})$ would have three tuples. DISTINCT turns the result into a set:

SELECT DISTINCT dept FROM Student;

dept
----
CS
EE
ME
(3 rows)

Expressions, aliases, sorting

SELECT sname AS name, age + 1 AS next_year, credits * 2 AS double_credits FROM Student, Course WHERE Student.sid = 1 AND Course.cid = 'C1';

name | next_year | double_credits
-----+-----------+---------------
Asha | 21        | 8
(1 row)

Columns may be computed and renamed with AS. Sorting and limiting:

SELECT sname, age FROM Student ORDER BY age DESC, sname LIMIT 3;

sname | age
------+----
Esha  | 23
Bilal | 22
Gita  | 22
(3 rows)

ORDER BY puts NULLs first or last depending on the system (here the NULL of Dara sorts as the smallest value, so it is last in descending order). Without ORDER BY the order of rows is not defined.

Conditions: ranges, lists, patterns, NULL

SELECT sname FROM Student WHERE age BETWEEN 21 AND 22 AND dept IN ('CS', 'EE');

sname
-----
Bilal
Chen
Farid
(3 rows)

BETWEEN a AND b includes both ends. A pattern uses % (any string, also empty) and _ (exactly one character):

SELECT sname FROM Student WHERE sname LIKE '_a%';

sname
-----
Dara
Farid
Hari
(3 rows)

The names whose second letter is a are Dara, Farid and Hari. NULL needs its own test: a comparison with NULL is UNKNOWN, so = NULL never selects anything:

SELECT sname FROM Student WHERE age = NULL;

sname
-----
(0 rows)

SELECT sname FROM Student WHERE age IS NULL;

sname
-----
Dara
(1 row)

A row is returned only if the condition is TRUE; age <> 21 also drops Dara, because NULL <> 21 is UNKNOWN. (The full truth tables are on the page on nested queries.)

Set operations

UNION, INTERSECT and EXCEPT combine the results of two queries with the same number of compatible columns and remove duplicates. UNION ALL keeps them (the SQL standard also has INTERSECT ALL and EXCEPT ALL; SQLite implements only UNION ALL).

SELECT dept FROM Student UNION SELECT dept FROM Course;

dept
----
CS
EE
ME
(3 rows)

SELECT dept FROM Student UNION ALL SELECT dept FROM Course;

dept
----
CS
EE
CS
ME
CS
EE
ME
CS
CS
CS
CS
CS
EE
ME
(14 rows)

The UNION has 3 rows, the UNION ALL has $8 + 6 = 14$.

SELECT sid FROM Student EXCEPT SELECT sid FROM Enrolled;

sid
---
4
(1 row)

EXCEPT is the algebra difference: the students with no enrolment.

Worked examples

Cheat box

Traps and GATE patterns

Connections

Joins and aggregation come next (joins and aggregates); constraints and triggers extend the DDL (constraints and triggers); the calculus gives the logical meaning (tuple relational calculus).

PYQs

A relation r(A, B) in a relational database  has 1200
tuples. The attribute A has integer values ranging from 6
to 20, and the attribute B has integer values ranging from
1 to 20. Assume that the attribute A and B  are
independently distributed.  The estimated number of
tuples in the output of σ(A>10)v(B=18)(r) is ______.

Consider the following relational schema:

Students(rollno: integer, name: string, age: integer,
cgpa: real)
Courses(courseno: integer,cname: string, credits:
integer)
Enrolled( rollno: integer,courseno: integer,grade:
string)

Which of the following options is/are correct SQL
query/queries to retrieve the names of the students
enrolled in course number (i.e. courseno) 1470?

Consider the following table named Student in a
relational database. The primary key of this table is
rollNum.

rollINum
name
gender
marks
1
Naman
M
62
2
Aliya
F
70
3
Aliya
F
80
4
James
M
82
5
Swati
F
65

The SQL query below is executed on this database.

SELECT *FROM Student
WHERE gender = 'F' AND  marks  > 65;
The number of rows returned by the query is
___________.

SELECT operation in SQL is equivalent to

SQL allows duplicates tuples in relations, and
correspondingly defines the multiplicity of tuples in the
result of joins. Which one of the following queries
always gives the same answer as the nested query
shown below:

select * from R

where a in (select S.a from S)

Consider a database table T containing two columns X
and Y each of type integer. After the creation of the table,
one record (X=1, Y=1) is inserted in the table. Let MX
and MY denote the respective maximum values of X and
Y among all records in the table at any point in time.
Using MX and MY, new records are inserted in the table
128 times with X and Y values being MX+1, 2*MY+1
respectively. It may be noted that each time after the
insertion, values of MX and MY change.

What will be the output of the following SQL query after
the steps mentioned above are carried out?

SELECT Y

FROM T

WHERE X=7;

Self-check

Revision card

References