SQL Commands and Scripts

SQL Commands are of two types DDL and DML

1. DDL – Data Definition Language
2. DML – Data Manipulation Language

1.DDL - Data Definition Language

These commands are used to create, modify and remove database and table structures.

DML – Data Manipulation Language

These are commands used to add, modify, delete and retrieve data stored in a relational database.

Examples for DDL Commands (Data Definition Language)

COMMAND	USE
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

Examples for DML Commands ( Data Manipulation Language)

Command	USE
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 #1: 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;