SELECT * FROM student;
Plus Two Computer Science · Chapter 09

Structured Query Language

12 sections
Interactive SQL playground
9 review questions
9.1

Structured Query Language

9.1

What is SQL?

Structured Query Language (SQL) is a language designed for managing data in a relational database management system (RDBMS). It provides an easy and efficient way to interact with relational databases. The original version was developed in the 1970s by Donald D. Chamberlin and Raymond F. Boyce at IBM's San Jose Research Laboratory, originally called Structured English Query Language (Sequel), later renamed SQL. In 1986, the American National Standard Institute (ANSI) published an SQL standard.

SQL provides facilities to create a table, insert data into a table, retrieve information from a table, modify data in the table, delete existing data, modify the structure of a table, and remove a table from a database, among other operations.

9.1.1

Features of SQL

Structured Query Language is an ANSI/ISO standard language for writing database queries. SQL is effective in framing queries because of the following features:

  • SQL is a relational database language, not a programming language like C or C++.
  • It is simple, flexible and powerful.
  • It provides commands to create and modify tables, insert data into tables, and manipulate data in the tables.
  • It gives guidelines to major popular RDBMS like Oracle, SQL Server, MySQL, MS Access, Sybase, Informix and Postgres.
  • SQL is a non-procedural language since it describes what data to retrieve, delete or insert, rather than how to perform the operation.
  • As part of data security, SQL provides facility to add or remove different types of access permissions to users on databases or tables.
  • It provides the concept of views.
9.1.2

Components of SQL

SQL has three components, each with a distinct role in developing an RDBMS:

DDLDMLDCL

Data Definition Language (DDL) deals with the schema (structure) definition of the RDBMS. DDL commands are used to create, modify and remove database objects such as tables, views and keys. The common DDL commands are CREATE, ALTER, and DROP.

Data Manipulation Language (DML) enhances efficient user interaction with the database system. DML permits users to insert data into tables, retrieve existing data, delete data from tables and modify the stored data. The common DML commands are SELECT, INSERT, UPDATE and DELETE.

Data Control Language (DCL) is used to control access to the database, essential for security concerns. DCL includes commands that administer privileges and commit data.

  • GRANT — allows access privileges to users on the database.
  • REVOKE — withdraws a user's access privileges previously given using GRANT.
9.2

Working on MySQL

9.2

Why MySQL?

ANSI standardised SQL in 1986 and ISO in 1987; the standard has evolved through several revisions since, with SQL:2011 the seventh revision. Despite these standards, different database software packages provide their own versions of ANSI SQL, so most SQL code is not completely portable without adjustment. This chapter uses the open-source database software MySQL.

MySQL is a free, fast, easy-to-use RDBMS developed, marketed and supported by MySQL AB, a Swedish company (later acquired by Sun Microsystems in 2008, which was in turn acquired by Oracle in 2010). It is popular because:

  • It is released under an open-source licence, so it is customizable.
  • It provides high security to the database.
  • It is portable, working on many operating systems and with many languages including PHP, PERL, C, C++ and JAVA.
  • It works rapidly and effectively even with large volumes of data.
  • It is highly compatible with PHP, a popular language for web development.
9.2.1–9.2.3

Opening MySQL, Creating and Opening a Database

We work on MySQL at the mysql> prompt, started from a terminal with mysql -u root -p. SQL is not case sensitive, but by convention commands/keywords are written in UPPER CASE and user-defined names (tables, columns) in lower case.

-- create and open a database
CREATE DATABASE school;
USE school;
-- list existing databases
SHOW DATABASES;

A database name must be unique — two databases in the same MySQL server cannot share a name. Opening a database with USE makes it the active database, so that all subsequent DDL, DML and DCL commands apply to it.

9.2.4

Data Types in SQL

A data type defines the type of value that may be entered in a column of a table, ensuring correctness of data. MySQL data types fall into three groups — numeric, string (text), and date and time.

Data typeUsageStorage (bytes)
TINY INTVery small integer values (−128 to 127)1
SMALL INTSmall integer (−32768 to 32767)2
INT / INTEGERNormal sized integer value4
BIG INTLarge integer value8
DEC(size,D) / DECIMAL(size,D)Exact numbers with fractional parts8
CHAR(x)Fixed-length string, 0–255 charactersx
VARCHAR(size)Variable-length string, 0–65535 charactersactual length
DATEYYYY-MM-DD, range 1000-01-01 to 9999-12-31—
TIMEHH:MM:SS—

CHAR is best for fixed-length data of the same small size (e.g. Gender 'M'/'F'); VARCHAR only allocates as much space as the actual data needs and is better for variable-length data like names.

9.3

SQL Commands

9.3

DDL and DML at a Glance

SQL provides commands to perform different operations on a database, classified as DDL, DML and DCL commands. DDL commands perform operations associated with the structure of the database — creation of tables, modification in the structure of tables and removal of tables. DML commands are associated with operations on the content of tables — insertion, retrieval, modification (updation) and deletion of records.

9.4

Creating Tables

9.4

The CREATE TABLE Command

Tables are the central and most important objects in any relational database. The DDL command CREATE TABLE defines a table by specifying its name and column definitions — name, data type and size, and constraints, if any. Every table must have at least one column.

CREATE TABLE student
(adm_no INT PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(20) NOT NULL,
 gender CHAR DEFAULT 'M',
 dob DATE,
 course VARCHAR(15),
 f_income INT);
9.4.1

Rules for Naming Tables and Columns

  • The name may contain letters (A–Z, a–z), digits (0–9), underscore (_) and dollar ($) symbol.
  • The name must contain at least one character; names with only digits are invalid.
  • The name must not contain white spaces or special symbols.
  • The name must not be an SQL keyword.
  • The name should not duplicate the name of another table in the same database, or another column in the same table.
9.4.2

Constraints

Constraints are rules enforced on data entered into a column, ensuring database integrity. They may be column-level (applied to individual columns) or table-level (applied to a group of columns, written at the end of the table definition).

  • NOT NULL — the column can never have NULL (empty) values. NULL is never equal to anything, not even another NULL.
  • AUTO_INCREMENT — MySQL assigns serial numbers automatically, starting at 1 and incrementing by 1; only one AUTO_INCREMENT column is allowed per table, and it must be the primary key.
  • UNIQUE — ensures no two rows have the same value in the specified column.
  • PRIMARY KEY — a combination of UNIQUE and NOT NULL, applied to one column or a combination of columns.
  • DEFAULT — sets a default value for a column when the user does not supply one.
CREATE TABLE stock
(icode CHAR(2) PRIMARY KEY AUTO_INCREMENT,
 iname VARCHAR(30) NOT NULL,
 dt_purchase DATE,
 rate DECIMAL(10,2),
 qty INT,
 UNIQUE (icode, iname));
9.4.3

Viewing the Structure of a Table

The DESCRIBE (or DESC) command displays the structure definitions of a table, and SHOW TABLES lists the tables created in the current database.

DESCRIBE student;  -- or DESC student;
SHOW TABLES;
9.5

Inserting Data into Tables

9.5

The INSERT INTO Command

The DML command INSERT INTO is used to insert tuples into tables.

INSERT INTO <table_name> [<column1>,<column2>,…,<columnN>]
VALUES(<value1>,<value2>,…,<valueN>);

If values are supplied for every column, in the same order as the table definition, the column list can be omitted. Multiple rows can be inserted with a single INSERT command by specifying several value lists separated by commas.

INSERT INTO student
VALUES (1001,'Alok','M','1998/10/2', 'Science', 24000);

INSERT INTO student (name, dob, course, f_income)
VALUES('Bharath','1999/01/01','Commerce',45000),
      ('Virat','1998/12/05','Science',22000);
  • The data type of each value must match its column.
  • CHAR/VARCHAR data must be enclosed in single or double quotes; DATE values are given within single quotes.
  • NULL values are specified as NULL, without quotes.
  • If no data is available for all columns, the column list must be included after the table name.
9.6

Retrieving Information from Tables

9.6

The SELECT Command

Before discussing the queries below, it helps to be familiar with the sample student relation used throughout this section.

adm_nonamegenderdobcoursef_income
1001AlokM1998-10-02Science24000
1002NikeM1998-11-26Science35000
1003BharathM1999-01-01Commerce45000
1004ViratM1998-12-05Science22000
1005MeeraF1998-08-15ScienceNULL
1006DivakarM1998-02-21ScienceNULL
1026NivedithaF1999-03-04Humanities52000

SELECT is used to retrieve information from specified columns in a table.

SELECT <column_name>[,<column_name>,…]
FROM <table_name>;

The keyword FROM is essential with SELECT. An asterisk (*) can substitute the complete list of columns to display every column.

9.6.1

Eliminating Duplicates — DISTINCT

By default, SELECT returns a value for every row, even if it repeats. The keyword DISTINCT eliminates duplicate values in the result; if the column contains more than one NULL, only one is shown. The keyword ALL (or omitting both DISTINCT and ALL) returns every value, duplicates included.

SELECT DISTINCT course FROM student;
9.6.2

Selecting Specific Rows — WHERE

SQL enables selection criteria for retrieval of records with the WHERE clause of SELECT. Conditions are expressed with relational operators (=, <> or !=, >, <, >=, <=) and logical operators (NOT, AND, OR).

SELECT * FROM student WHERE gender='F';
SELECT name, course, f_income FROM student
WHERE course='Science' AND f_income<25000;

SQL also has special operators for setting conditions:

  • BETWEEN…AND — specifies a range of values (inclusive of both bounds), e.g. f_income BETWEEN 25000 AND 45000.
  • IN — checks whether a value matches any in a given list, e.g. course IN ('Commerce','Humanities').
  • LIKE — matches patterns using wildcards % (any substring) and _ (any single character), e.g. name LIKE '%ar'.
  • IS NULL / IS NOT NULL — tests whether a column contains (or doesn't contain) a NULL value, e.g. f_income IS NULL.
9.6.3

Sorting Results — ORDER BY

The result of a query can be sorted in ascending or descending order of some column values using the ORDER BY clause, with ASC or DESC keywords (default is ascending). Only the displayed results are sorted — the order of records in the table stays unchanged. If used together with WHERE, ORDER BY must appear after it.

SELECT * FROM student ORDER BY f_income DESC;
SELECT name, course FROM student ORDER BY course, name;
9.6.4

Aggregate Functions

MySQL provides built-in functions that operate on an aggregate of tuples and return a single value:

FunctionReturn value
SUM()Total of the values in the column
AVG()Average of the values in the column
MIN()Smallest value in the column
MAX()Largest value in the column
COUNT()Number of non-NULL values in the column (COUNT(*) counts every row)
SELECT MAX(f_income), MIN(f_income), AVG(f_income) FROM student;
9.6.5–9.6.6

GROUP BY and HAVING

The rows of a table can be grouped together based on a common value in a column using GROUP BY; tuples with the same value in the specified attribute are placed in one group, and aggregate functions can then be applied to each group.

SELECT course, COUNT(*), AVG(f_income)
FROM student
GROUP BY course;

The HAVING clause applies a condition to form groups (not to individual rows), and is always used along with GROUP BY.

SELECT course, COUNT(*) FROM student
GROUP BY course
HAVING COUNT(*) > 3;
9.7

Modifying Data in Tables

9.7

The UPDATE Command

The DML command UPDATE changes the values in one or more columns of specified rows, using the keyword SET to give the new data. Changes may be applied to all rows, or restricted to selected rows using WHERE. The new data can be a constant, an expression, or data from other tables.

UPDATE <table_name>
SET <column_name> = <value> [,<column_name> = <value>,…]
[WHERE <condition>];
UPDATE student
SET f_income = f_income + 1000
WHERE f_income < 25000;

Columns holding NULL values can also be updated in this way, e.g. setting f_income = 20000 WHERE f_income IS NULL.

9.8

Changing the Structure of a Table

9.8

The ALTER TABLE Command

The DDL command ALTER TABLE modifies the structure of a table — adding or dropping columns, changing the data type or size of existing columns, or renaming a table.

9.8.1

Adding a New Column

ALTER TABLE <table_name>
ADD <column_name> <data_type>[<size>] [<constraint>]
[FIRST | AFTER <column_name>];

If the position is not specified, the new column is added at the end. A newly added column contains only NULL values initially.

9.8.2

Changing a Column's Definition — MODIFY

ALTER TABLE <table_name>
MODIFY <column_name> <data_type>[<size>] [<constraint>];
9.8.3–9.8.4

Removing a Column — DROP, and Renaming a Table

ALTER TABLE <table_name> DROP <column_name>;
ALTER TABLE <table_name> RENAME TO <new_table_name>;

A table can be renamed even while it still contains tuples.

9.9

Deleting Rows from a Table

9.9

The DELETE Command

The DML command DELETE removes individual rows or a set of rows from a table, selected using the WHERE clause. If WHERE is omitted, all rows in the table are deleted. DELETE removes only records, never individual field values — to blank out just a column's value, use UPDATE with SET <column> = NULL instead.

DELETE FROM <table_name> [WHERE <condition>];
9.10

Removing a Table from a Database

9.10

The DROP TABLE Command

An unwanted table can be removed from the database using the DDL command DROP TABLE. It removes the table permanently, even if it contains tuples — once dropped, the data cannot be recovered.

DROP TABLE <table_name>;
9.11

Nested Queries

9.11

A Query Inside a Query

A nested query places the result of one query dynamically inside the condition of another. The inner query is called the sub query, and the query containing it is the outer query. SQL first evaluates the sub query within the WHERE clause, then substitutes the result into the condition of the outer query. When using relational operators, the sub query must return a single-row result.

SELECT name, course FROM student
WHERE f_income = (SELECT MAX(f_income)
              FROM student);
9.12

Concept of Views

9.12

Views as Virtual Tables

A view is a virtual table that does not really exist in the database, but is derived from one or more tables, called base tables. The tuples of a view are not physically stored anywhere — only the definition of the view is stored in the database. A view can be created with the DDL command CREATE VIEW.

CREATE VIEW <view_name>
AS SELECT <column_name1> [,<column_name2>,…]
FROM <table_name>
[WHERE <condition>];

All DML commands can be used with a view much as with a table — but because a view accesses its base table, operations like UPDATE and DELETE performed through a view actually reflect in the base table. If a view is no longer needed, it can be removed with DROP VIEW without affecting the base table(s).

DROP VIEW <view_name>;

Views save storage space by letting the same table be used as different (virtual) tables, support sharing along with privacy, and reduce the complexity of WHERE conditions during retrieval, updation or deletion.

Try It Yourself

SQL SELECT Playground

Choose a WHERE condition, the columns to SELECT, and how to ORDER BY on the student table, then press Run — this mirrors how a SELECT statement is built up clause by clause.

SELECT … FROM student WHERE … ORDER BY …
SELECT adm_no, name, course, f_income FROM student;
Self-Assessment

Question & Answer Review

Tap a question to reveal the answer — a quick way to check understanding of SQL concepts.

01What are the three components of SQL, and what does each do?
DDL (Data Definition Language) deals with the schema/structure of the database using commands like CREATE, ALTER and DROP. DML (Data Manipulation Language) handles the content of tables using SELECT, INSERT, UPDATE and DELETE. DCL (Data Control Language) controls access to the database using GRANT and REVOKE.
02Differentiate between CHAR and VARCHAR data types.
CHAR is a fixed-length character type that always uses the specified amount of space, padding with white spaces if the actual data is shorter. VARCHAR is a variable-length type that allocates space only for the actual size of the string, saving memory — better suited to columns like names where the length varies.
03What is the difference between a PRIMARY KEY constraint and a UNIQUE constraint?
Both ensure that no two rows share the same value in the specified column(s), but a PRIMARY KEY additionally enforces NOT NULL — it can be considered a combination of UNIQUE and NOT NULL. Only one PRIMARY KEY is allowed per table, whereas a table can have multiple UNIQUE constraints.
04What operator is used for pattern matching in SQL, and what do its wildcards mean?
The LIKE operator is used for pattern matching. The wildcard % matches any substring of characters (including none), while _ (underscore) matches exactly one character. Patterns are case sensitive.
05Distinguish between the WHERE clause and the HAVING clause.
WHERE places a condition on individual rows and is evaluated before any grouping takes place. HAVING places a condition on groups formed by GROUP BY, and is always used along with it — it filters which groups appear in the result, not which individual rows do.
06How do COUNT(*) and COUNT(column_name) differ?
COUNT(*) counts every row that satisfies the condition, since the asterisk stands for the whole record. COUNT(column_name) counts only the non-NULL values present in that specific column among the selected rows, so it can return a smaller number than COUNT(*) when the column contains NULLs.
07What happens if the WHERE clause is omitted from a DELETE FROM statement?
All the rows in the table are deleted, since there is no condition to restrict which rows are affected. The table itself still remains in the database (unlike DROP TABLE), just with no data in it.
08What is a view, and where is it actually stored?
A view is a virtual table derived from one or more base tables; it does not physically exist in the database. Only the definition of the view (its underlying SELECT query) is stored — its contents are generated afresh from the base table(s) whenever the view is used.
09Can updating data through a view affect the base table? Explain.
Yes. Since a view has no data of its own and is simply a window onto its base table, DML operations like UPDATE and DELETE performed on a view actually act on, and are reflected in, the base table the view is derived from.