Quartz 5

05 SQL statements (part 1)

8 min read

SQL statements (part 1)

Structured Query Language (SQL) statements (part 1)

Structured Query Language (SQL) = a standard computer language for the operation and management of relational databases

  • Used to query, insert, update, and modify data
  • In a DBMS, SQL is used to:
  • Create database and table structures
  • Perform basic data management chores (insert, update and delete)
  • Perform complex queries to transform raw data into useful information

4 categories of SQL commands (3 & 4 not in syllabus?)

  1. Data Definition Language (DDL)
  • Defines database schema
  • Create database, table structure, objects
CommandDescription
CREATE
Create database, table and objects
CREATE DATABASE database-name
E.g. CREATE DATABASE schooldb
ALTERAlter structure of the database
TRUNCATERemove all records from a table
DROPDelete database, table and objects
RENAMERename an object (e.g. Table) in the database
COMMENTAdd comments to the data dictionary
  1. Data Manipulation Language (DML)
  • Use to retrieve and modify data
CommandDescription
SELECTRetrieve records from 1 or more table
To query data from tables, based on specific condition(s)
SELECT [DISTINCT] expressions
FROM table
[WHERE conditions]; # […] is optional
Use wild card (*) to select all fields from a table
E.g. Select * FROM Student
⇒ Select ALL columns and ALL records from Student table
E.g. Select * FROM Student WHERE ClassID=2
⇒ Select ALL columns from Student table with condition that ClassID=2
Can select specific columns from a table
E.g. SELECT ID, Name, NRIC FROM Student WHERE ClassID=2;
⇒ Select ID, Name, NRIC from Student table where ClassID=2
Select statement with where conditions
E.g. SELECT ID, Name, NRIC FROM Student
WHERE age > 17 AND age < 21;1
E.g. SELECT ID, Name, NRIC FROM Student
WHERE age >21 OR age <17;
E.g. SELECT ID, Name, NRIC FROM Student
WHERE age = 17 OR age = 18;
(X): WHERE age = 17 OR 18
E.g. SELECT ID, Name, NRIC FROM Student
WHERE age BETWEEN 18 AND 20;
Includes those age 18, 19, and 20
E.g. SELECT ID, Name, NRIC FROM Student
WHERE age NOT BETWEEN 18 AND 20;
Exclude those age 18, 19, and 20
Select those age below 18 and above 20
E.g. SELECT ID, Name, NRIC FROM Student
WHERE ClassID IN (1,2,3);
Where classID = 1 or = 2 or =3
E.g. SELECT ID, Name, NRIC FROM Student
WHERE ClassID NOT IN (1,2);
Where classID != 1 and classID != 2
E.g. SELECT Country, CompanyName, ContactName, Phone, Fax FROM Supplier WHERE Country NOT IN (‘UK’, ‘USA’, ‘Germany’);
Note quotation mark for strings else error!
E.g. SELECT ID, Name, NRIC FROM Student
WHERE name like ‘%TAN%’;
‘TAN’ in the name
E.g. SELECT ID, Name, NRIC FROM Student
WHERE name like ‘TAN%’;
Name starts with ‘TAN’
E.g. SELECT ID, Name, NRIC FROM Student
WHERE name like ‘%TAN’;
Name ends with ‘TAN’
SELECT * from customer WHERE fax IS NULL;
List customers without Fax number
SELECT distinct customerID FROM [Order]
List customers (customerID) who have placed at least 1 order (Order table)
Order is a reserved keyword in SQL
SELECT DISTINCT ClassID, Name FROM Student
Records with distinct ClassID and Name
Select with Order by clause
SELECT expressions FROM tables
[WHERE conditions]
[ORDER BY expression [ASC | DESC]]
E.g. SELECT Name, NRIC FROM Student
WHERE ClassID=2 Order by NRIC DESC,
Name ASC
Note: Order by NRIC, no need Order by Student.NRIC
Where ClassID=2 and order the result by NRIC (descending) and Name (ascending)
Sorts NRIC first by DESC, then if NRIC same then sort Name by ASC (?)2
SELECT country, companyname FROM customer Order by country, Companyname desc
List country and companyname of customer with the country in ascending order and companyname in descending order
Count(): an aggregate function that returns no. of rows in a group
SELECT COUNT(*) FROM tables [WHERE condition]
E.g. SELECT COUNT(*) FROM STUDENT WHERE GENDER = ‘F’
E.g. to list no. of product in the order with Order ID = 10251 (orderdetail)
(/): SELECT COUNT(*) AS [No. of Orders] FROM orderdetail WHERE orderid=10251
(X): SELECT COUNT(Id) FROM Order WHERE Id = 10251
⇒ notice OrderDetail is a table on its own, so don’t take data from Order table
SUM(): an aggregate function that returns the sum of the non-NULL values or only the distinct values in a group
SELECT SUM(FIELD)
FROM tables
[WHERE conditions]
E.g. SELECT SUM(ProductId3) as [Total Quantity] FROM OrderDetail WHERE OrderId = 10252
Total Quantity will be header for result table
AVG(): an aggregate function that calculates the average value of all non-NULL values within a group
SELECT AVG(FIELD)
FROM tables [WHERE conditions]
E.g. SELECT AVG(age) as [AGE]
FROM Student
WHERE ClassID=2
MAX(): an aggregate function that returns the non-NULL maximum value of all values in a group
SELECT MAX(FIELD)
FROM tables [WHERE conditions]
MIN(): an aggregate function that returns the non-NULL minimum value of all values in a group
SELECT MIN(FIELD)
FROM tables
[WHERE conditions]
E.g. SELECT MAX(Quantity) AS [Maximum Quantity], MIN(Quantity) AS [Minimum Quantity] FROM OrderDetail
INSERTInsert a new row
UPDATEUpdate existing row
DELETEDelete a row
  1. Data Control Language (DCL)
  • Use to control access to the database
  1. Transaction Control Language (TCL)
  • Use to manage changes to a database, usually at transactional level
Create table
CREATE TABLE table-name (
column-name1 data-type1(size) [NULL|NOT NULL],
column-name2 data-type2(size) [NULL|NOT NULL],
…
);
E.g.
CREATE TABLE Classes(
ClassID INTEGER PRIMARY KEY AUTOINCREMENT,4
Name TEXT(20) NOT NULL
);
E.g.
CREATE TABLE Student(
StudentID INTEGER PRIMARY KEY AUTOINCREMENT,
Name TEXT(100) NOT NULL,
NRIC TEXT(15) NOT NULL
);
  • Name of table is usually singular
  • Not null = not empty
  • PK = Primary Key (should not have >1 field as PK unless composite keys)
  • AI = Autoincrement
  • Unique = require it to be unique
  • Cannot be for e.g. class
  • Note: write lines 1-6 in theory papers
  • Add new records from here
Alter table
  • Change structure of an existing table
  • Rename a table
ALTER TABLE existing_table_name
RENAME TO new_table_name;
E.g.
ALTER TABLE Classes
RENAME TO Class;
  • Add new column to a table
Alter TABLE table-name
Add column-name1 data-type1(size);
E.g.
Alter TABLE Student
ADD ClassID INTEGER;
Alter TABLE Student
ADD Gender TEXT
  • Modify an existing column in a table
ALTER TABLE table_name
MODIFY column_name column_type;
or
ALTER TABLE table_name
ALTER column_name column_type;
  • OR for practicals, can also:
  • Drop an existing column in a table
ALTER TABLE table_name
DROP COLUMN column_definition;
  • Unlike SQL-standard and other database systems, SQLite supports a very limited functionality of the ALTER STATEMENT ⇒ SQLite ALTER statement can only:
  • Rename a table
  • Add a new column to a table
  • Rename a column (added supported in version 3.20.0)
Drop table
  • Remove an existing table (one at a time)
DROP TABLE [IF EXISTS] existing_table_name; # [ ] means optional
  • If you drop a non-existence table, SQLite issues an error
  • If IF EXISTS option is used, SQLite removes the table only if it exists, otherwise it just ignores the statement and does nothing
  • If a foreign key constraint is violated, error message issued, table will not be dropped
  • Note: DROP TABLE removes table from database and the file on disk completely
  • Cannot undo and recover from this action

Foreign key

  • Rows might be inserted into Student table that do not correspond to any row in the Class table
  • Due to bug in application or editing of database using other 3rd party’s software
  • Rows might be deleted from the Class table, leaving orphaned rows in the Student table that do not correspond to any of the remaining rows in Class table
  • Might cause the application or applications to malfunction later on, or at least make coding the application more difficult
  • SQL foreign key constraints are used to enforce relationships between table (e.g. between Class and Student table)
  • Enforcement is done by DBMS
  • Assumption: any applications that use the database will assume that for each row in the student table there exists a corresponding row in the class table
  • The constraint enforces referential integrity by guaranteeing that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table
  • To add an SQL foreign key constraint to the database schema, a foreign key definition may be added by modifying the declaration of the student table to this:
CREATE TABLE Student(
StudentID INTEGER, Name TEXT, NRIC TEXT,
GENDER TEXT, ClassID INTEGER,
FOREIGN KEY(ClassID) REFERENCES Class(ClassID);

  • With foreign key constraint added, DBMS will check that the record for Joyce Lim in Student table can’t be added because class with ID=5 in the Class table does not exist. The database will throw an error and action of inserting will be aborted
  • ⇒ Need to add/insert a new record in Class table with ID = 5 before we can add record in Student table with class ID = 5

  • With foreign key constraint added, DBMS will check whether any records in Student table refers to Class with ID=2. If there are, the database will throw an error and the action to remove the record in Class will be aborted
  • ⇒ Need to remove/delete all records that has FK=2 in Student table before we can delete the class with ID=2 in the Class Table

Northwind Database Schema

SELECT name, class
from STUDENT

Note: delete previous command to run subsequent ones in ‘execute SQL’

Use ‘…’ if name of table contains e.g. “OF”

Date: YYYYMMDD

⇒ so can organise chronologically / numerical order

Exercises (my ans)

Exercise: List all records in the Category table
SELECT * FROM Category
Exercise: List Company Name, Contact Name, Address, City,
Region, Postal Code of Customers (all records) (Customer Table)
SELECT CompanyName, ContactName, Address, City, Region, PostalCode FROM Customer
Exercise: List Company Name, Contact Name, Address, City,
Region, Postal Code of Customers in Mexico (Country) (Customer
Table)

  • My ans:
    CREATE TABLE Student(‘Student ID’ INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, NRIC TEXT NOT NULL)
CREATE TABLE Classes(ClassID INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NULL)

  • Note: cannot do all at once?
    ALTER TABLE Student

ADD ClassID INTEGER

ADD Gender TEXT

Comments from the Word document

Footnotes

  1. Comment by ANDREA TAN KAI XUAN HCI: need ’;’ for theory? cos both works on practical right ↩

  2. Comment by ANDREA TAN KAI XUAN HCI: is this correct
    if it is, then since NRIC will not be the same, what’s the point of sorting name by ASC? ↩

  3. Comment by ANDREA TAN KAI XUAN HCI: why not quantity? ↩

  4. Comment by ANDREA TAN KAI XUAN HCI: ??? ↩

Graph View

Backlinks