SQL Commands and Scripts

SQL Commands are of two types DDL and DML.

DDL - Data Definition Language commands are used to create, modify and remove database and table structures. Whereas, DML - Data Manipulation commands are used to add, modify, delete and retrieve data stored in a relational database.

DDL (Data Definition Language)

CREATE DATABASE	Creates a database
CREATE TABLE	Creates a table definition
ALTER TABLE	Modifies the definition of a table
PRIMARY KEY	Sets primary key for a table
FOREIGN KEY...REFERENCES	Sets foreign for a table

DML ( Data Manipulation Language)

SELECT...FROM	Used for retrieving data from a database.
WHERE	Used for retrieving only the rows that match a given condition in a query.
ORDER BY	Sorts the result of a query by a given column in ascending order
GROUP BY	Arranges data into groups.
INNER JOIN	Combines rows from different tables if the join condition is true.
SUM	Returns the sum of a numerical column.
COUNT	Counts the number of rows where the column is not NULL.
AVG	Returns the average of values in a numerical column.

Datatypes used in SQL commands:

DATATYPE	DESCRIPTION
CHARACTER	Fixed length text
VARCHAR(n)	Variable length text
BOOLEAN	True or False ( SQL uses 1 and 0)
INTEGER	Whole number
REAL	Numbers with decimal places
DATE	A date formatted as YYYY-MM-DD
TIME	A time formatted as HH:MM:SS

Examples of DDL 

Example: Write SQL command to create a database called School and a table Student with the following attributes.

STUDENT(StudentID, FirstName, SecondName, DateOfBirth, ClassID)

 

ANSWER:

CREATE DATABASE School;

CREATE TABLE Student (
	StudentID CHARACTER,
	FirstName CHARACTER,
	SecondName CHARACTER,
	DateOfBirth DATE,
	ClassID CHARACTER );

 

 Adding the primary key after the table is created:

ALTER TABLE Student ADD PRIMARY KEY (StudentID);

Setting the primary key during table creation:

CREATE TABLE Student (
	StudentID CHARACTER PRIMARY KEY,
	FirstName CHARACTER,
	SecondName CHARACTER,
	DateOfBirth DATE,
	ClassID CHARACTER );

 

Example: Create Class table, make ClassID the primary key and make LicenceNumber the foreign key that links the Class table with the Student table.

CLASS(ClassID, Location, LicenceNo)

CREATE TABLE Class (
	ClassID CHARACTER PRIMARY KEY,
	Location CHARACTER,
	LicenceNumber CHARACTER );

Now adding the foreign key to the Student table:

ALTER TABLE Class ADD FOREIGN KEY ClassID REFERENCES Class(ClassID);

 

Delete the table Class

DROP TABLE Class;

 

SQL - DML Commands example

 

Example: To return values from specific columns in a table.

Display the First name, Last name and Date of Birth of all students from the table Students.

SELECT FirstName, LastName, DateOfBirth FROM Students;

 

Display all records from students table:

SELECT * FROM Students;

 

Example: SELECT with WHERE

Display the First name and Last name of all the students who were born after 31/12/2012 from the following table.

STUDENT(StudentID, FirstName, SecondName, DateOfBirth, ClassID)

ANSWER:

SELECT FirstName, Last Name

FROM Students

WHERE DateOfBirth > 31/12/2012;

 

Display the First name and Last name of all the students who were born after 31/12/2012 from the following table sorted in the ascending order of StudentID.

SELECT FirstName, Last Name

FROM Students

WHERE DateOfBirth > 31/12/2012

ORDER BY StudentID;

 

Example:  INSERT INTO is used to add a new row to a table.

 Adding a row into the following table.

STUDENT(StudentID, FirstName, SecondName, DateOfBirth, ClassID)

INSERT INTO Students(3234, Mark, Twain, 15/10/2010, 11B);

Another way of using this command is:

INSERT INTO Students (StudentID, FirstName, SecondName)

VALUES(3234, Mark, Twain);

 

Example:  DELETE

Deleting all records from Students table:

DELETE * FROM Students;

 

Deleting some records from Students table based on the criteria:

DELETE FROM Students

WHERE StudentID = 1234;

 

Example:  SUM, AVG, MAX, MIN Functions

Display the total price of all the items from the following table.

PRODUCTS(ProductID, ItemName, Category, Price)

SELECT SUM(Price)FROM Products;

 

Display the average price of products from the Products table.

SELECT AVG(Price)FROM Products;

 

Display the lowest price of products from the Products table.

SELECT MIN(Price)FROM Products;

 

Display the highest price of products from the Products table.

SELECT MAX(Price)FROM Products;

 

Example:  INNER JOIN

INNER JOIN combines rows from different tables if the join condition is TRUE.

Syntax:

SELECT Table1.column1, Table2.column2

FROM Table1 INNER JOIN Table2

ON Table1.common_field = Table2.common_field;

 

Consider the following two tables:

STUDENT(StudentID, FirstName, SecondName, DateOfBirth, ClassID)

CLASS(ClassID, Location, LicenceNo)

SELECT Student.FirstName, Student.SecondName, Class.Location

FROM Student INNER JOIN Class

ON Student.ClassID = Class.ClassID;