SQL

DATABASE AND TABLES
Database: A database is an organized
collection of related data.
Relational database: In relational database
data is stored in the form of tables.
Tables: A table stores data in the form of
rows and columns.
For eg. In a student’s database there will be
Students, Teachers and Office staff tables
for storing respective data. Database
Tables
Table
Columns
R
o
ws

RDBMS TERMINOLOGY
 Tuple/Row/Record: Horizontal subset of a table is known as the tuple.
 Attributes/Column: Vertical subset of a table is known as the attribute.
 Cardinality: Total number of rows in a table is called the cardinality of the table.
 Arity/Degree: Total number of columns of a table is called the Degree of the table.
 Primary Key: A column or a group of columns of a table which uniquely identifies each record of a
table is called Primary Key.
 Candidate Key: A column or a group of columns of a table which can be used as the Primary key of a
table is called the candidate key.
 Alternate Key: A candidate key of a table which is not made its Primary key is called its Alternate key.

RDBMS TERMINOLOGY Contd…….
Admn_no Roll_no Name Class
1001 1 Ankita 12
1002 2 Vaibhav 12
1003 3 Utsav 12
In the Student table:
 Cardinality = 3 (there are 3 rows in this table)
 Degree = 4 (there are 4 columns in this table)
 Primary Key = Admn_no
 Candidate Key = Roll_no (because it can become primary key anytime)
 Alternate Key = Roll_no (out of Admn_no and Roll_no, Admn_no has become
the Primary key thus Roll_no is the alternate key.
Student table

CATEGORIES OF SQL COMMANDS
SQL
COMMANDS
DDL
CREATE
DATABASE
CREATE
TABLE
ALTER TABLE DROP TABLE
DML
SELECT UPDATE INSERT INTO DELETE
DCL
GRANT REVOKE
SQL command can be classified into categories:
 Data Definition Language (DDL): These commands are used to create and change the structure of the table.
 Data Manipulation Language(DML): These commands are used to display or change the data stored in the
table.
 Data Control Language(DCL): These commands are used to control the access to database and tables.

DATA TYPES IN SQL
CLASS DATA TYPE DESCRIPTION
TEXT char
varchar
A fixed-length string from 1 to 255 characters in
A variable-length string from 1 to 255 characters in
length
(Values must be enclosed in single quotes or double
quotes.)
NUMERIC decimal(size,d)
Integer(size)/int(size)
Represents number with or without decimal
Used for storing integer values upto size digits
DATE date It represents the date including: day, month, year
Various data types supported by MYSQL are:

CREATING & OPENING A DATABASE
 Create database : This command is used to create a database.
Q. Write a command to create a database student.
Ans. create database student;
 Use <database name>: This command is used to open the database.
Q. Write a command to open the database student.
Ans. use student;
Note: Please put semicolon after completing sql command.

CREATING A TABLE
 create table: This command is used to create a table.
Syntax:
CREATE TABLE <TableName>(<ColumnName1> <Data Type1>, <ColumnName2> <Data
Type2>,… ,<ColumnNameN> <Data TypeN>);
Q Write a command to create a table STUDENT having columns roll number as integer, name
as character(20), class as integer, date of birth as date and total as float.
Ans. create table STUDENT( roll_no int(2), name char(20), dob date, total decimal(5,2));

CREATING A TABLE contd….
To verify that tables table has been created , give the
command:
Show tables;
We can view the structure of the table STUDENT by giving the
command:
Desc student;

ADDING RECORDS TO TABLE
Insert into: This command is used to add row to the table.
Syntax:
INSERT INTO <tablename> (<column1>, <column2>, ..., <columnn>) VALUES (<value1>,
<value2>, ... <value n>);
Q. Insert a record with values 1, Rajat Kumar, 6-12-2005, 450 into the table STUDENT
Ans. insert into STUDENT values(1, ‘Rajat Kumar’, ‘2015-12-6’,450);
or
insert into STUDENT (roll_no, name, dob, total) values (1, “Rajat Kumar”, ‘2015/12/6’,450);
Note:
 In the first case the data values for each column must match exactly the default order in which they appear in the table.
 The values for char and date data types must be enclosed in quotes. Standard date format is "yyyy-mm-dd".

TABLES: EMP, DEPT
Consider the tables EMP and
DEPT to show the results of
all further queries.
EMP table
DEPT table

DISPLAYING RECORDS
Select command: The select command is used to display data stored in a table.
 To display all attributes of all records:
Q. Write a command to display all records from the table emp.
Ans. Select * from emp;
 To display selected attributes of all records:
Q. Write a command to display employee number and name of all employees.
Ans. Select empno, ename from emp;
Note: Please use the column names as given in the table in the sql command.

ELIMINATING DUPLICATE VALUES
 DISTINCT keyword: The distinct keyword is used to eliminate
duplicate values and display the duplicated data only once.
Q. Give a command to display different types of jobs
present in the table emp.
Ans. Select distinct job from emp;

SELECT WITH AIRTHMETIC OPERATORS
Arithmetic operators can be used in the select command to perform
calculations.
Q. Write a command to display the name and annual salary of all
employees from the table emp.
Ans. Select name, sal*12 from emp;
Q. Write a command to display employee number and bonus of all
employees where bonus is calculated as 10% of salary.
Ans. Select eno, sal*10/100 from emp;
Q. Write a command to display the result of the expression: 24*5/2
Ans. Select 24*5/2;
+ addition
- subtraction
* multiplication
/ division
% Remainder of
division
ARITHMETIC OPERATORS

USING COLUMN ALIAS
 Column alias lets different name to appear for a column than the actual
one in the output. Column alias does not rename the column. It simply
displays a different column name in the output. You can use AS
keyword to specify the alias name. But As keyword is optional.
Q. Write a command to display employee name and salary and
display salary as Monthly salary.
Ans. Select ename, sal as “Monthly Salary” from emp;
or
Select ename , sal from emp;

PUTTING TEXT IN QUERY OUTPUT
Text can also be displayed in the query output. For eg.
Select ename, “earns” , sal , “Rs” from emp;

SELECT COMMAND: WHERE CLAUSE
Where clause is used to display selected rows based upon the
condition specified.
Q. Give a command to display records of those employees whose
job is manager.
Ans. Select * from employee where job=“MANAGER”;
Q. Give a command to display employee number, name and salary
of those employees whose salary is more than 30000.
Ans. Select empno, ename, sal from employee where sal>30000;

RELATIONAL OPERATORS IN WHERE
CLAUSE
= Equal to
< Less than
> Greater than
>= Greater than equal to
<= Less than equal to
!= or
<>
Not equal to
RELATIONAL OPERATORS
Relational operators can be used in the where clause.
Q. Write a command (WAC) to display name and salary of those
employees whose salary is greater than and equal to 10000.
Ans. Select ename, sal from emp where sal>=10000;
Q. Write a command to display employee number, department number
of those employees whose department number is not 20.
Ans. Select empno, deptno from emp where deptno
!= 20;

LOGICAL OPERATORS IN WHERE CLAUSE
NOT Negates a condition
AND
/ &&
The condition is true if both the
conditions joined using AND are
true
OR /
||
The condition is true if any of the
conditions joined using AND are
true
LOGICAL OPERATORS
Logical operators are used to join two conditions.
Q. WAC to display employee number and name of those employees
whose salary is greater than 10000 and less than 5000.
Ans. select empno, ename from emp where sal >5000 and sal< 10000;
Q. WAC to display employee number and name of those employees who
are ANALYST or CLERK.
Ans. Select empno, name from emp where job=“ANALYST” or
job=“CLERK”;
Q. WAC to display employee number of those employees who are not
manager.
Ans. Select empno from emp where not (job = “MANAGER”;

BETWEEN OPERATOR – RANGE OF VALUES
The between operator is used to define the range of values. The range includes both the
upper and lower values.
Q. WAC to display employee number and name of those employees whose salary is greater
than equal to 10000 and less than equal to 5000.
Ans. select empno, ename from emp where sal between 5000 and 10000;
Q. WAC to display employee number and name of those employees whose salary is NOT
greater than equal to 10000 and less than equal to 5000.
Ans. select empno, ename from emp where sal not between 5000 and 10000;

IN OPERATOR – LIST OF VALUES
IN operator is used to select value/values from a list of values.
Q. WAC to display name of employees who are manager, clerk and analyst.
Ans. Select ename from emp where job in (“MANAGER”, “CLERK”,ANALYST”);
Q. WAC to display name of all employees except manager, clerk and analyst.
Ans. Select ename from emp where job not in (“MANAGER”, “CLERK”,ANALYST”);

LIKE CLAUSE – PATTERN MATCHING
LIKE clause is used to fetch data from the table which matches a specified pattern. It uses two wild
card characters:
% (percentage): It is used to represent any sequence of zero or more characters.
_ (underscore): It is used to represent a single character.
Q. WAC to display names of those employees whose name starts with A.
Ans. Select ename from emp where ename like “A%”;
Q. WAC to display names of those employees whose name ends with A.
Ans. Select ename from emp where ename like “%A”;
Q. WAC to display names of those employees whose name contains A.
Ans. Select ename from emp where ename like “%A%”;
Q. WAC to display names of those employees whose name is exactly 5 characters long.
Ans. Select ename from emp where ename like “_ _ _ _ _”;

NULL
NULL is used to represent unknown value. NULL is not the same as zero or a space or any other
character. In a table NULL is searched for using IS NULL/IS NOT NULL keywords.
Q. WAC to display name of those employees whose commission is NULL.
Ans. Select ename form emp where comm is NULL;
Q. WAC to display name of those employees whose commission is not NULL.
Ans. Select ename form emp where comm is not NULL;

ORDER BY – ARRANGING THE RESULT
ORDER BY clause is used to arrange the results of the select command in either ascending or descending
order. The default order is ascending order. However you can also add ASC keyword for ascending order. To
arrange the data in descending order use the keyword DESC. It can also be used to arrange the data in
particular order on multiple columns.
Q. WAC to display the data of employees in ascending order of their salary.
Ans. Select * from emp order by sal; or select * from emp order by sal asc;
Q. WAC to display the data of employees in descending order of their name.
Ans. Select * from emp order by ename desc;
Q. WAC to display the data of employees in descending order of their name and within that in ascending
order of salary .
Ans. Select * from emp order by ename desc , sal

INSERTING NULL VALUES
There are two ways of inserting NULL values in the column/columns of the table.
 If we don’t have data for particular columns we can leave them at the time of giving INSERT INTO
command. For those columns NULL values are automatically inserted.
Q. WAC to insert employee number as 7771, name as AKSHAT and depart number as 20. All other
columns will have Null values.
Ans. Insert into emp (empno, ename, deptno) values (7771, “AKSHAT”,20);
 We can also insert NULL values explicitly.
Q. WAC to insert employee number as 7771, name as AKSHAT and depart number as 20. All other
columns will have Null values.
Ans. Insert into emp (empno, ename,job, mge, hiredate, comm, deptno) values (7771,
“AKSHAT”,NULL, NULL, NULL, NULL, 20);

UPDATE STATEMENT
The UPDATE statement is used to modify the data present in the table.
Syntax:
UPDATE <table_name>
SET <column name> = <value>, [ <column name> = <value>, …] [WHERE <condn>];
Q. WAC to increase the salary of all employees by Rs 2000.
Ans. Update emp set sal=sal +2000;
Q. WAC to increase the salary by 20% of all MANAGER.
Ans. Update emp set sal=sal +sal*0.20 where job= ‘MANAGER’;
Q. WAC to increase the salary by 2000Rs and change department number to 20 for employee whose
employee number is 7521.
Ans. Update emp set sal=sal +2000, deptno=20 where empno=7521 ;

DELETE STATEMENT
DELETE is used to delete rows from the table.
Syntax:
DELETE FROM < tablename> [ Where < condn>];
Q. WAC to delete data of those employee whose salary is less than 6000.
Ans. Delete from emp where sal<6000;
Q. WAC to delete all rows of table employee.
Ans. Delete from emp;

AGGREGATE FUNCTIONS
Aggregate functions work on multiple rows. There are 5types of aggregate functions:
Aggregate function Purpose
MAX() To find the maximum value under the
specified column
MIN() To find the minimum value under the
specified column
AVG() To find the average of values under the
specified column
SUM() To find the sum of values under the
specified column
COUNT() To count the values under the specified
column

AGGREGATE FUNCTION – MAX()
Purpose Command Result
To find the highest salary paid to an
employee
Select max(sal) from emp; Max (sal)
95700
To find the highest salary paid to
MANAGERS
Select max(sal) from emp where
job=‘MANAGER’;
Max (sal)
60000
To find the highest salary +
commision paid to employee
Select max(sal+comm*sal) from
emp;
Max(sal+comm)
95700
Please refer to table employee for the queries given below:

AGGREGATE FUNCTION – MIN()
To find the lowest salary paid to an
employee
Select min(sal) from emp; Min (sal)
9600
To find the lowest salary paid to
MANAGERS
Select min(sal) from emp where
job=‘MANAGER’;
Min (sal)
15000
To find the lowest salary +
paid to employee
Select min(sal+comm*sal) from
emp;
Min(sal+comm)
9600

AGGREGATE FUNCTION – AVG()
The argument of AVG() function can be of numeric (int/decimal) type only. Averages of String
and Date type data are not defined.
AVG() function considers only non NULL values in that column.
Purpose Command
To find the average salary paid to an
employee
Select avg(sal) from emp;
To find the average salary paid to
MANAGERS
Select avg(sal) from emp where
job=‘MANAGER’;

AGGREGATE FUNCTION – SUM()
The argument of AVG() function can be of numeric (int/decimal) type only. Averages of String
and Date type data are not defined. Please refer to table employee for the queries given below:
Purpose Command
To find the sum of salary paid to all
employees
Select sum(sal) from emp;
To find the sum of salary paid to
MANAGERS
Select sum(sal) from emp where
job=‘MANAGER’;

AGGREGATE FUNCTION – COUNT()
To count the total number of employees
in the table
Select count(*) from emp; Count(*)
14
To count the different types of jobs that
the table has
Select count(distinct jobl) from
emp;
Count(distinct job)
5
To count the number of employees who
are paid commision
Select count(comm) from emp; Count(comm)
1
COUNT() takes one argument which can be any column name, an expression based on a column, or
an asterisk (*).
 COUNT(column name) returns the number of non-NULL values in that column
 COUNT(* ) returns the total number of rows satisfying the condition

GROUP BY CLAUSE
Group by clause is used we need to group the data of the table based on certain type.
Q WAC to display the maximum salary paid to employee of each type of job.
Ans. Select job, max(sal) from emp group by job;
We can include more than one aggregate function in one command.
QWAC to display maximum, minimum, sum of salary paid to employee of each type of
department.
Ans. Select deptno, max(sal), min(sal), sum(sal) from emp group by deptno;
Note: Please make sure to include the column name on which we are grouping in the select command.

HAVING CLAUSE
Having clause is used to apply condition on groups ie when condition is applied on aggregate
function. We can include more than one aggregate function in one command.
Q WAC to display the maximum salary paid to employee of each type of job where maximum salary
is greater than 10000.
Ans. Select job, max(sal) from emp group by job having max(sal)>10000;
QWAC to display maximum, minimum, sum of salary paid to employee of each type of department
for departments 10 and 20.
Ans. Select deptno, max(sal), min(sal), sum(sal) from emp group by deptno having deptno=10 or
deptno=20;
Note: WHERE is used to put a condition on individual row of a table whereas HAVING is used to put condition on individual group formed by
GROUP BY clause in a SELECT statement.

ADDING, MODIFYING AND DELETING COLUMNS
ALTER TABLE command is used to add, modify and delete columns from a table.
Q. WAC to add a new column age of type integer to the table emp;
Ans. Alter table emp add age integer;
Q. WAC to drop a column age from the table emp.
Ans. Alter table emp drop age;
Q. WAC to drop column age and job from the table emp.
Ans. Alter table emp drop age, drop job;
Q. WAC to change the size of ename column to char(30).
Ans. Alter table emp modify ename char(30);

CARTESIAN PRODUCT OR CROSS JOIN
Cartesian product of two tables is a table obtained by pairing each row of one table with each row of another
table. The table obtained has
columns = sum of the columns of both the tables
rows = product of the rows of both the tables
It is represented by the symbol X
Consider the following tables A and B
Table A Table B Cartesian Product A X B
Roll name
1 Ajay
2 vimal
Class section
11 A
12 B
Roll Name Class Section
1 Ajay 11 A
2 Vimal 11 A
1 Ajay 12 B
2 Vimal 12 B
Q WAC to obtain the Cartesian product of
table A and B
Ans. Select * from A, B ;

EQUI JOIN
 When we extract data from two tables they must have one column which is present in both
the tables. An equi join of two tables is obtained by putting an equality condition on the
Cartesian product of two tables.
 This equality condition is put on the common column of the tables and is called equi join
condition.
 It is mandatory to give equi join condition when we want to extract data from two tables. Let
us consider two tables: emp and dept
Q. WAC to display employee number, job, salary, department name and loction from the tables
emp and dept.
Ans. Select empno, job, sal, dname, loc from emp, dept where emp.deptno = dept.deptno;
Note: When a column exist with same name in both the tables it is preceded by table name and dot to avoid ambiguity.

EQUI JOIN Contd….
Q. WAC to display employee name, salary and depart name of all MANAGERS from emp and
dept tables.
Ans. Select ename, sal, dname from emp, dept where emp.deptno = dept.deptno and job =
“MANAGER”;
Q. WAC to display employee name, salary and depart name of all employee in ascending
order of salary from emp and dept tables.
Ans. Select ename, sal, dname from emp, dept where emp.deptno = dept.deptno order by
sal;
Note: Equi join is nothing but a Cartesian product of two tables in which a condition of equality is enforced on the
column which is present in both the tables.

FOREIGN KEY AND REFRENTIAL INTEGRITY
Equi join is obtained from the Cartesian product by giving equality condition on the common column
which is present in both the tables. This common column is the primary key of one table and in other table
it is called foreign key.
Foreign key is a column in one table which is the primary key of another table.
For eg. The column Deptno is the primary key of dept table and the foreign key of emp table.
It is important that no entry should be made in the emp table in which deptno does not belong to dept
table ie we cannot write deptno in emp table which does not exist in dept table.
Referential integrity is the property of a relational database which ensures that no entry in a
foreign key column of a table can be made unless it matches a primary key value in the
corresponding related table.

UNION
Union of two tables is a table in which the number of columns is same as the number of columns of the
two tables and number of rows is the sum of the number of rows of both the tables.
Union operation can be applied on two tables only if the tables have same number and type of
columns. The syntax of union is:
SELECT <select_list>
FROM <tablename>
[WHERE <condition> ]
UNION [ALL]
SELECT <select_list>
FROM <tablename>
[WHERE <condition> ];
Union does not display any duplicate rows unless ALL is specified with it.

UNION Contd…..
Consider two tables : CLASS XI CLASS XII
ROLL
NO
NAME SECTION
1 AJAY A
2 BIMAL B
ROLL NAM SEC
2 BIMAL B
3 HARSH B
4 VIJAY C
Q. WAC to produce a combined list of all students from class XI and XII eliminating the duplicate rows.
Ans. Select * from classxi union select * from classxii;
Q WAC to produce a combined list of all students from class XI and XII without eliminating the duplicate rows.
Ans. Select * from classxi union all select * from classxii;
Note: In the first question roll no 2, which is present in both the tables will appear only once but in the second question it will appear twice.

SQL

More Related Content

What's hot

Similar to SQL

More from Vineeta Garg

Recently uploaded

SQL