Introduction to SQL

P212 INFORMATION MANAGEMENT

SQL (Structured Query Language)

 System R project in the early 1970s. The Sequel

language has evolved since then, and its name has
changed to SQL (Structured Query Language).
Many products now support the SQL language. SQL
has clearly established itself as the standard
relational database language.
 It is pronounced as S-Q-L or sometime See-Qwell.
SQL (Structured Query Language)

 The SQL language has several parts:

• Data-definition language (DDL). The SQL DDL
provides commands for defining relation schemas,
deleting relations, and modifying relation schemas.
• Data-manipulation language (DML). The SQL
DML provides the ability to query information from
the database and to insert tuples into, delete tuples
from, and modify tuples in the database.
Data-definition language (DDL)
Data-manipulation language (DML)

A query takes as input several tables (possibly

only one) and always returns a single table.
Data-manipulation language (DML)

 Select Statement
 The most commonly used SQL command is SELECT
statement. It is used to query the database and retrieve
selected data that follow the conditions we want.
 Syntax:

SELECT expressions
FROM tables
WHERE conditions;
Optional clauses in SELECT statement

 [WHERE Clause] : It specifies which rows to retrieve.

 [GROUP BY Clause] : Groups rows that share a property so
that the aggregate function can be applied to each group.
 [HAVING Clause] : It selects among the groups defined by
the GROUP BY clause.
 [ORDER BY Clause] : It specifies an order in which to
return the rows.
SQL WHERE
A WHERE clause in SQL is a data manipulation
language statement.
 WHERE clauses are not mandatory clauses of SQL
DML statements. But it can be used to limit the
number of rows affected by a SQL DML statement
or returned by a query.
 Actually.it filters the records. It returns only
those queries which fulfill the specific conditions.
 Syntax: SELECT column1, column 2, ... column n
FROM table_name
WHERE [conditions];
Conditional Selection in WHERE Clause
Select Statement

For example, let a database table: student_details;

Select Statement
 From the table student_details:
 From the table student_details:
 Query: Select the first name of
 Query: Select all the
all the student information of the Students
 Syntax:
 Syntax:
Select *
Select first_name
from student_details;
from student_details;
 Output:
 Output:
SQL SELECT DISTINCT

 The SQL DISTINCT command is used with SELECT key word

to retrieve only distinct or unique data.
 In a table, there may be a chance to exist a duplicate
value and sometimes we want to retrieve only unique
values. In such scenarios, SQL SELECT DISTINCT statement
is used. (same with Select Unique Statement)
 Syntax:
SELECT DISTINCT column_name ,column_name
FROM table_name;
SQL SELECT DISTINCT

 Query: show the distinct home towns of the students

 Syntax: SELECT DISTINCT home_town FROM students;
 Output:
SQL SELECT COUNT
 The
SQL COUNT() function is used to return the
number of rows in a query.
 TheCOUNT() function is used with SQL SELECT
statement and it is very useful to count the
number of rows in a table having enormous data.
 Forexample: If you have a record of the voters in
selected area and want to count the number of
voters then it is very difficult to do it manually
but you can do it easily by using the SQL SELECT
COUNT query.
SQL SELECT COUNT
 The SQL COUNT() function is used to return the number of
rows in a query.
 The COUNT() function is used with SQL SELECT statement
and it is very useful to count the number of rows in a
table having enormous data.
 For example: If you have a record of the voters in selected
area and want to count the number of voters then it is
very difficult to do it manually but you can do it easily by
using the SQL SELECT COUNT query.
 Syntax: SELECT COUNT (expression)
FROM tables
WHERE conditions;
SQL SELECT COUNT
 SQL SELECT COUNT(column_name)
Syntax: SELECT COUNT(name) FROM employee_table;
(It will return the total number of names of employee_table. But null fields will not
be counted.)

 SQL SELECT COUNT(*)

Syntax: SELECT COUNT(*) FROM employee_table;
The "select count(*) from table" is used to return the number of records in table.

 SQL SELECT COUNT(DISTINCT column_name)

Syntax: SELECT COUNT(DISTINCT name) FROM
employee_table;
It will return the total distinct names of employee_table.
SQL SELECT TOP
 The SQL SELECT TOP Statement is used to select top data
from a table. The top clause specifies that how many rows
are returned. If a table has a large number of data, select
top statement determines that how many rows will be
retrieved from the given table.
 Syntax: Select top (rows) * from table_name;
SQL SELECT TOP

 Query: Show the top 2 rows from your Table employee

 Syntax: SELECT TOP 2 * FROM employee;
 Output:
SQL SELECT RANDOM
 The SQL SELECT RANDOM() function returns the random
row. It can be used in online exam to display the random
questions.
 There are a lot of ways to select a random record or row
from a database table. Each database server needs
different SQL syntax.
 Syntax: SELECT column FROM table ORDER BY RAND ( )
LIMIT 1
SQL SELECT IN
 SQL IN is an operator used in a SQL query to help reduce
the need to use multiple SQL "OR" conditions.
 It is used in SELECT, INSERT, UPDATE or DELETE
statement.
 Advantage of SQL SELECT IN It minimizes the use of SQL
OR operator.
 Syntax:
 SELECT *
 FROM table_name
 WHERE field_name IN (value 1, value 2 ... value n)
SQL SELECT DATE
 SQL SELECT DATE is used to retrieve a date from a
database. If you want to find a particular date from a
database, you can use this statement.
 Query: let's see the query to get all the records after
'2013-12-12’.
 Syntax:
SELECT * FROM table-name WHERE your date-column >=
‘2013-12-12’ ;
SQL SELECT DATE
 Another query to get all the records after '2013-12-12' and
before '2013-12-13' date.
Syntax: SELECT* FROM table-name where your date-
column < '2013-12-13' and your date-column >= '2013-12-12’
 To compare the dates within the query, you should use
BETWEEN operator to compare the dates.
 SELECT * FROM table_name WHERE yourdate BETWEEN
'2012-12-12' and '2013-12-12'
SQL AND

 The SQL AND condition is used in SQL query to create two

or more conditions to be met. The SQL AND condition
requires that both conditions should be met.
 Syntax: SELECT columns FROM tables WHERE condition 1
AND condition 2;
 In discrete mathematics:
 T AND T = T
 T AND F = F
 F AND T = F
 F AND F = F
SQL OR

 The SQL OR condition is used in a SQL query to create a

SQL statement where records are returned when any one
of the condition met.
 Syntax: SELECT columns FROM tables WHERE condition 1
OR condition 2;
 In discrete mathematics:
 T OR T = T
 T OR F = T
 F OR T = T
 F OR F = F
SQL NOT

 The SQL NOT condition is used in a SQL query to create a

SQL statement where records are returned the reciprocate
value of the data.
 Syntax: SELECT columns FROM tables WHERE NOT value;
 In discrete mathematics:
 NOT T = F
 NOT F = T
SQL ORDER BY Clause

 The SQL ORDER BY clause is used for sorting data in

ascending and descending order based on one or more
columns.
 Some databases sort query results in ascending order by
default.
 Syntax:
SELECT expressions FROM tables WHERE conditions ORDER
BY expression [ASC | DESC];
SQL ORDER BY Clause

 QUERY: SHOW ALL THE INFORMATION OF THE EMPLOYEES

AND SORT THEIR SALARY AND NAME IN ASCENDING ORDER.
 Syntax: SELECT * FROM CUSTOMERS ORDER BY NAME, SALARY;
SQL ORDER BY CLAUSE WITH
DESCENDING ORDER:
 Thisstatement is used to sort data in descending
order. You should use the DESC attribute in your
ORDER BY clause as follows.
 SELECT column
 FROM table_name
 WHERE column = ‘value'
 ORDER BY column DESC;
SQL ORDER BY CLAUSE WITH
DESCENDING ORDER:

 Query: list all the information of the employee and sort the names in descending
order.
 Syntax: SELECT * FROM CUSTOMERS ORDER BY NAME DESC;
 Any questions?

 Activities
on Introduction to SQL was posted in our
moodle class.