Structured Query Language (SQL): One Mark Questions, Book Back Answers and Notes

About Structured Query Language (SQL)

Medium ~120 min study

An RDBMS keeps data in tables, but you need a language to ask it for anything. That language is SQL. With it you create a table, add rows, change values, remove rows and pull out exactly the records you want. MySQL, Oracle and SQLite all understand SQL, with small differences.

The chapter goes in a sensible order. First come the data types, such as char, varchar, int, dec and date. Then Data Definition Language (DDL) builds the table, Data Manipulation Language (DML) fills and edits it, and Data Query Language (DQL) reads from it with SELECT. After that come constraints, which reject bad data, and the TCL commands that save or undo changes.

Expect to be asked for the components of SQL with one command from each, the difference between DELETE, TRUNCATE and DROP, the types of constraints, and short queries on a given table. Practise writing each query in full with the clauses in the right order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. One misplaced clause and the query throws an error.

What you'll learn

• Name the five components of SQL and give the commands that belong to each.
• Write CREATE TABLE statements with suitable data types and a primary key.
• Apply NOT NULL, UNIQUE, CHECK, DEFAULT and PRIMARY KEY constraints correctly.
• Add, change and remove records with INSERT, UPDATE and DELETE.
• Write SELECT queries using DISTINCT, WHERE, ORDER BY, GROUP BY and HAVING.
• Use COMMIT, ROLLBACK and SAVEPOINT to control a transaction.

Before you start

• The ideas of table, row and column from the Database Concepts chapter.
• How the relational model represents real-world entities as tables.
• Basic data types for text, numbers and dates.

Structured Query Language (SQL) explained

How SQL Is Organised

What SQL Does

An RDBMS stores data in tables made of rows and columns. SQL is the standard language used to talk to it. With SQL you can create a database and its tables, insert and change records, and retrieve records for an application or report. SQL keywords are not case sensitive, but writing them in capitals makes a query easier to read.

The Five Components of SQL

DDL (Data Definition Language) defines the structure of tables: CREATE, ALTER, DROP and TRUNCATE. DML (Data Manipulation Language) works on the rows: INSERT, UPDATE and DELETE. DCL (Data Control Language) gives or takes away permissions: GRANT and REVOKE. TCL (Transaction Control Language) manages changes made by DML: COMMIT, ROLLBACK and SAVEPOINT. DQL (Data Query Language) retrieves data: SELECT.

Creating and Changing Tables

CREATE TABLE names each column and gives its data type and size, for example Name char(20). ALTER TABLE changes an existing table: ADD a column, MODIFY its type, rename it with CHANGE, or DROP COLUMN. TRUNCATE TABLE removes all rows but keeps the structure. DROP TABLE removes the table itself, structure and data together.

Constraints

A constraint is a rule that stops invalid data entering a table. It can be a column constraint (written with one column) or a table constraint (written after all columns, often to join two columns into one key). NOT NULL means the field must have a value. UNIQUE means no two rows can have the same value; in the textbook it is used only on fields also declared NOT NULL. CHECK allows only values that meet a condition, such as Age <= 19. DEFAULT supplies a value when none is given. PRIMARY KEY identifies each row uniquely and cannot be NULL, and a table can have only one primary key.

Adding and Changing Records

INSERT INTO adds a row. The values must follow the column order of the table, unless you list the column names in the statement. UPDATE changes values in the rows that match the WHERE condition. DELETE FROM removes the matching rows but leaves the table structure in place. Leaving out WHERE in UPDATE or DELETE affects every row.

Querying with SELECT

SELECT retrieves columns from a table. DISTINCT removes duplicate rows from the output. WHERE filters rows using relational operators, AND, OR, NOT, BETWEEN, IN and their negations. ORDER BY sorts the output (ASC by default, or DESC). GROUP BY collects rows with the same value into one group, usually with COUNT, SUM, AVG, MAX or MIN, and HAVING filters those groups.

Transaction Control

Changes made by DML are not permanent until they are committed. COMMIT saves them permanently. ROLLBACK undoes changes back to the last COMMIT. SAVEPOINT marks a point inside a transaction, and ROLLBACK TO that savepoint undoes only the changes made after it.

Common mistakes to avoid

• Leaving out the semicolon at the end of a statement. End every SQL command with a semicolon.
• Declaring a column UNIQUE without NOT NULL. The textbook rule is that UNIQUE is applied only to fields declared NOT NULL, so write both.
• Writing UPDATE or DELETE without a WHERE clause. Without WHERE the change applies to every row in the table.
• Using WHERE to filter groups. WHERE filters individual rows before grouping; HAVING filters the groups made by GROUP BY.
• Giving INSERT values in the wrong order. Follow the column order of the table, or name the columns in the INSERT statement.

Test yourself on these with the practice test, then check the worked reasoning in the solved MCQs.

Frequently asked questions

What is the difference between SQL and MySQL?

SQL is a language for working with relational databases. MySQL is an RDBMS, a piece of software that stores the data and understands SQL commands.

What is the difference between a PRIMARY KEY and a UNIQUE constraint?

Both stop duplicate values. A table can have only one primary key, and it can never be NULL. A table can have several UNIQUE columns; in the textbook these columns are also declared NOT NULL.

What happens if I run DELETE without a WHERE clause?

Every row in the table is removed. The table structure stays, so you can insert rows again. If the change has not been committed, ROLLBACK can bring the rows back.

How are DELETE, TRUNCATE and DROP different?

DELETE is a DML command that removes the rows matching a condition, or all rows if there is no WHERE. TRUNCATE is a DDL command that removes all rows, keeps the structure and frees the space. DROP removes the whole table, structure and data.

When should I use HAVING instead of WHERE?

Use WHERE to filter rows before they are grouped. Use HAVING to filter groups after GROUP BY, for example HAVING COUNT(*) > 2. A condition that uses a group function like COUNT or SUM belongs in HAVING.

What does COMMIT do?

COMMIT makes the changes of the current transaction permanent. After COMMIT, a ROLLBACK cannot undo those changes, and other users of the database can see them.

How do ROLLBACK and SAVEPOINT work together?

ROLLBACK undoes changes that have not been committed, taking the table back to its last committed state. If you have marked a SAVEPOINT, ROLLBACK TO that savepoint undoes only the changes made after it.