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. 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	Arrange 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:

• 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

Question: Write an SQL command to create the database School.

ANSWER:

CREATE DATABASE School;

Question: Create the table Student with the following attributes.

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

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

Question: Make StudentID field in the above table the primary key.

ALTER TABLE Student ADD PRIMARY KEY (StudentID);

Question: Create the table Student with the following attributes and set the field StudentID as the primary key.

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

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

Question: Create the table Class, 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);

Question: Delete the table Class.

DROP TABLE Class;

SQL – DML Commands example

Question: 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;