XI.

1 Introduction to SQL
SQL (Structured Query Language) is a database query language developed by IBM in the 1970s
as a way of getting information into and out of relational DBMSs. A fundamental difference
between SQL and standard programming languages is that SQL is declarative, that is, the user has
to specify what kind of data are required from the database and the RDBMS is responsible for
figuring out the way to retrieve it.

XI.2 2 Basic operations in a RDB

In a relational database, three basic operations are used to develop useful sets of data:
selection, projection and join.
 The selection (𝝈) operation retrieves certain records from a relation based on the
user-specified criteria.
 The projection (𝚷) operation extracts fields from a relation, permitting the user to
create new relations that contain only the required information.
 The join operation combines the data from the two relations based on a common
column, providing the user with more information than is available in individual
relations.

Together, these three operations are part of relational algebra.

Data Types
When the table is defined every field in it is assigned a data type. The type of a data value
both defines and constrains the kinds of operations, which may be performed on it. Some of
the most commonly used SQL data types are as follows:

• CHAR(size): It defines a fixed size-length character string (can contain letters,

numbers and special characters), where size can be a maximum of 255.
• VARCHAR(size): It defines a variable length character string (can contain letters,
numbers and special characters) of up to size characters.
• NUMBER(size): It defines an integer-type data with maximum number of digits up
to size specified in parenthesis.
• DATE: It is used to store date. By default, the format is YYYY-MM-DD.
• NUMBER(size, decimal): It holds numbers with fractions. The maximum numbers
of digits are specified in size. The maximum number of digits to the right of the
decimal is specified in decimal.

XI.3 SQL Basic Commands

SQL commands can be divided into two main sublanguages: DDL and DML

• Data Definition Language: DDL is used to create and delete database and its objects.
These commands are primarily used by the DBA during the building and removal
phases of a database project. The most important DDL statements in SQL are as
follows:
- CREATE TABLE: To create a new table.
- ALTER TABLE: To modify the structure of a table.
- DROP TABLE: To delete a table.
 - TRUNCATE: Remove all records of a table
- RENAME
• Data Manipulation Language: DML is used to retrieve, insert, modify and delete
database information. These commands will be used by all database users during the
routine operation of the database. The most important DML statements in SQL are the
following:
- INSERT: To insert data into a table.
- UPDATE: To update data in a table.
- DELETE: To delete data from a table.
- SELECT: To retrieve data from a table.

NOTE: All SQL queries must be terminated by a semicolon (;) even if the statement extends
over many lines.

XI.3.1 CREATE TABLE Command

The CREATE TABLE command is used to define the structure of the table.

Syntax
CREATE TABLE table_name
(Column1 data type,
Column2 data type,
Column3 data type,
…………..);
The column parameters specifies the names of the columns of the table.
Example
CREATE TABLE persons (
PersonID int,
LastName varchar (255),
FirstName varchar (255),
Address varchar (255)
City varchar (255)
);

The empty “persons” table will now look like this:

PersonID LastName FirstNam Address
e

The empty “persons” table can now be filled with data with the SQL INSERT INTO statement

XI.3.2 ALTER TABLE Command

The ALTER TABLE command allows a user to change the structure of an existing table.

• New columns can be added with the ADD clause.

• Existing columns can be modified with the MODIFY clause.
• Columns can be removed from a table by using the DROP clause.

1. ALTER TABLE-ADD column

To add a new column in a table, use the following syntax:

Syntax Eg
ALTER TABLE table_name ALTER TABLE customers
ADD column_name datatype; ADD Email varchar(225);
 The SQL statement above adds an “Email” column to the customers table
2. ALTER TABLE-DROP COLUMN

To delete a column in a table, use the following syntax.

Syntax Example
ALTER TABLE table_name ALTER TABLE customers
DROP COLUMN column_name; DROP column Email;

The SQL above deletes the “Email” column from the “customers”.

3. ALTER TABLE- ALTER/MODIFY DATATYPE

To change the data type of a column in a table, use the following syntax:

Syntax Syntax
ALTER TABLE table_name ALTER TABLE table_name
ALTER COLUMN column_name MODIFY COLUMN column_name
Data type; data type;

EXAMPLE
Persons_table
LastName FirstName Address City
Owen Peter SWR Limbe
Ojong Patrick SWR Mamfe
Fombi Shanta NWR Bamenda

Now if we want to add a column named “DateofBirth” in the table above, we use the following SQL statement
ALTER TABLE person
ADD DateofBirth;

DROP TABLE Command

The DROP TABLE command removes the table definition (with all records). In other words, SQL
DROP TABLE is used to delete the tables in our database.
• Columns can be removed from a table by using the DROP clause.
Syntax: Examples:
DROP TABLE <tablename>; DROP TABLE Employee;
The above SQL command will delete the EMPLOYEE table.
XI.3.3 INSERT Command
The INSERT command is used to insert or add rows (records) into the specified table.
Syntax:
INSERT INTO tablename ( column1,
column2, ..., columnN) VALUES (value1,
value2, ..., valueN);
Examples Explanation
1 INSERT INTO Employee ( example 1 will add a new record at the
Code, Deptt, Name, Address, Salary) bottom of the EMPLOYEE table
VALUES (101, 'RD01', 'Prince', 'Park Way', consisting of the values in parenthesis.
15000);
 Note that for each of the listed columns, a matching value must be specified. In case
nocolumn list is specified, then a value must be given for each column and in the same order as
specified in the CREATE TABLE command.

XI.3.4 UPDATE Command

The UPDATE command is used for modifying attribute values of records in a table.
Syntax:
UPDATE tablename
SET column1 = value1,
column2 = value2,
columnN = valueN
WHERE condition;
Examples Explanation
1 UPDATE EMPLOYEE example 1 command will update (in our case,
SET Salary = Salary + 1000; increments) the Salary field with 1000 for all the records.
2 UPDATE EMPLOYEE Example 2 command will increment the Salary column
SET Salary = Salary + 1000 with 1000 for only those rows that comply with
WHERE Deptt = 'RD01'; condition specified in WHERE clause (Deptt = 'RD01').

XI.3.5 DELETE Command

The DELETE command is used to delete all or selected records from the specified table.
Syntax: Example:
DELETE FROM tablename DELETE FROM Employee
WHERE condition; WHERE Salary > 8000;
NOTE: If WHERE condition is not used in the DELETE command, then all the records from the
specified table will be deleted.

XI.3.6 SELECT command

The SELECT statement is used to query the database and retrieve selected data. We
can fetch either the entire table or according to some specified rules. The data
returned is stored in a result table. This table is also called result set.
Syntax
SELECT column1, column2, column3,…, columnN
FROM tablename;
 Column1, column2, column3…columnN are the table
columns
 Table name is the table from where we select the data
Example
SELECT first_name, last_name
FROM customer;
Here the SQL command selects the first_name and last_name of all
customers in the customers table.
 To select all the columns of a table, use * instead of column list with
SELECT.
SELECT*
FROM customers;
SQL SELECT WHERE clause
A SELECT statement can have an optional WHERE clause. The WHERE clause allows us to fetch records
from a database table that matches specified condition(s). for example,
SELECT*
FROM customers
WHERE last_name = ‘Doe’;
Here, the SQL command selects all customers from the customers table with the last_name Doe.

What is the result set for the following SQL command?

SELECT age, country
FROM customer
WHERE country = USA.

XI.4 SQL Joins

The SQL Joins clause is used to combine records from two or more tables in a database. A
JOIN is a means for combining fields from two tables by using values common to each.
Oid
Consider the following two tables, (a) CUSTOMERS table is as follows:

(a) CUSTOMERS (b) ORDERS

ID Name Age Address Salary Date Cust_id Amount
1 Ramesh 32 Ahmedabad 2000 102 2009-10-08 3 3000
2 Khilan 25 Dehli 1500 100 2009-10-08 3 1500
3 kaushik 23 Chaitali 2000 101 2009-11-20 2 1560
4 Chaitali 25 Mumbai 6500 103 2008-05-20 4 2060
5 Hardik 27 Bhopal 8500
6 Komal 22 MP 4500
7 Muffy 24 Indore 10000

Query This would produce the following result:

SELECT ID, Name, Age, Amount ID Name Age Amount
FROM CUSTOMERS, ORDERS 3 kaushik 23 3000
WHERE [Link] = 3 kaushik 23 1500
2 Khilan 25 1560
ORDERS.CUST_ID;
4 Chaitali 25 2060
Now, let us join these two tables in our SELECT statement as follow
 Here, it is noticeable that the join is performed in the WHERE clause. Several operators can
be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can
all be used to join tables. However, the most common operator is the equal symbol.

SQL Join Types:

There are different types of joins available in SQL:

 INNER JOIN: returns rows when there is a match in both tables.

 LEFT JOIN: returns all rows from the left table, even if there are no matches in the
right table.
 RIGHT JOIN: returns all rows from the right table, even if there are no matches in
the left table.
 FULL JOIN: returns rows when there is a match in one of the tables.
 SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily
renaming at least one table in the SQL statement.
 CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two
or more joined tables.

QUERRY APPLICATION

1) The table E (for EMPLOYEE) nr name salary

1 John 100
5 Sarah 300
7 Tom 100
Query Solution Query Solution
select salary salary select nr, salary nr salary
from E from E
100 1 100
300 5 300
7 100
select * nr name salary select * nr name salary
from E from E
where salary < 200 1 John 100 where salary < 200 7 Tom 100
7 Tom 100 and nr >= 7
select name, salary name salary
from E
where salary < 200 John 100
Tom 100
 2) Cartesian product:

Query 3) Natural
enr 1 E
Theename
table
join Bill A dname
(for dnr
dept EMPLOYEE) from E,table
The
dnr Ddname
D=(for1DEPARTMENT)
ename dept dnr dname

The table E (for EMPLOYEE) The table D (for DEPARTMENT)

nr name dept nr name
1 Bill A A Marketing
2 Sarah C B Sales
3 John A C Legal

Query Solution
select * enr ename dept dnr dname
from E as E(enr, ename, dept), 1 Bill A A Marketing
D as D(dnr, dname)
2 Sarah C C Legal
where dept = dnr
3 John A A Marketing
select *
nr name dept nr name
from E, D
where dept = [Link] 1 Bill A A Marketing
2 Sarah C C Legal
3 John A A Marketing