Basic SQL

Contents

• Part 1. SQL Language – Basic

• Part 2. SQL Language – Advanced

Reference:
[Link]
[Link]

TRƯ Ờ NG CÔNG NGHỆ THÔNG TIN VÀ TRUYỀN THÔNG

School of Inform ation and Com m unication Technology

2
PART 1. SQL LANGUAGE - BASIC

• Datatypes
• Create, Drop, Select Database
• Create, Insert Table
• Where
• And & Or
• Update
• Delete
• Like
• Top
• Order by
• Group by
• Distinct

3
 Data Types
Type Description Example Values

serial autoincrementing four-byte integer 1, 2, 3,

integer, -5000, 150000

A 4-byte integer type (default integer type).
int

numeric(p,s) A numeric type with user-defined precision (p) 123.45, -987.654321

and scale (s).

char(n) A fixed-length character type. 'A', 'Hello '

‘A’, 'Bob'
varchar(n) A variable-length character type

“Đại Cồ Việt, Hà Nội”

text Variable unlimited length

date Stores calendar dates (year, month, day). 2024-10-08

timestamp Stores both date and time (without time zone). 2024-10-08 14:30:00

boolean Stores true, false, and null values. true, false, NULL
TRƯ Ờ NG CÔNG NGHỆ THÔNG TIN VÀ TRUYỀN THÔNG
School of Inform ation and Com m unication Technology

4
Create Database

• CREATE DATABASE statement is used to

create a new SQL database.
• Syntax
CREATE DATABASE DatabaseName;
• Note: database name should be unique within
the RDBMS.
• Example:
CREATE DATABASE dbexample;
\l

TRƯ Ờ NG CÔNG NGHỆ THÔNG TIN VÀ TRUYỀN THÔNG

School of Inform ation and Com m unication Technology

5
Drop Database

• DROP DATABASE statement is used to drop an

existing database in SQL schema
• Syntax
DROP DATABASE DatabaseName;
• Note:
• database name should be unique within the RDBMS.
• You cannot drop the current database. Connect to
another database first.
• Example:
DROP DATABASE dbexample;
\l
TRƯ Ờ NG CÔNG NGHỆ THÔNG TIN VÀ TRUYỀN THÔNG
School of Inform ation and Com m unication Technology

6
Change Database

• Change database in PostgreSQL

• Syntax
\c DatabaseName;
Note: database name should be unique within
the RDBMS.
• Example:
\l
\c postgres
You are now connected to database
"postgres" as user ...

TRƯ Ờ NG CÔNG NGHỆ THÔNG TIN VÀ TRUYỀN THÔNG

School of Inform ation and Com m unication Technology

7
Create Table

• SQL CREATE TABLE statement is used to create a new table.

• Syntax
CREATE TABLE table_name(
column1 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);
• Example:
CREATE DATABASE dbexample;
\c dbexample
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
age INT,
address TEXT NOT NULL,
salary NUMERIC(10,2)
);
TRƯ Ờ NG CÔNG NGHỆ THÔNG TIN VÀ TRUYỀN THÔNG
School of Inform ation and Com m unication Technology

8
Desc Table

\d customers;

TRƯ Ờ NG CÔNG NGHỆ THÔNG TIN VÀ TRUYỀN THÔNG

School of Inform ation and Com m unication Technology

9
Drop Table

• DROP TABLE statement is used to remove a table

definition and all the data, indexes, triggers,
constraints and permission specifications for that
table.
• Syntax
DROP TABLE table_name;
• Example:
DROP TABLE customers;
\d customers;
Did not find any relation named "customers"
TRƯ Ờ NG CÔNG NGHỆ THÔNG TIN VÀ TRUYỀN THÔNG
School of Inform ation and Com m unication Technology

10
Insert Query

• INSERT INTO Statement is used to add new rows of

data to a table in the database.
• Syntax
INSERT INTO TABLE_NAME (column1, column2,
column3,...columnN)
VALUES (value1, value2, value3,...valueN);
• Example:
INSERT INTO CUSTOMERS (name,age,address,salary)
VALUES ('Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (name,age,address,salary)

VALUES
(‘Peb', 25, 'Delhi', 1500.00),
(‘kaushik’, 23, ‘Kota’, 2000.00);

TRƯ Ờ NG CÔNG NGHỆ THÔNG TIN VÀ TRUYỀN THÔNG

School of Inform ation and Com m unication Technology

11
Sample data

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+

TRƯ Ờ NG CÔNG NGHỆ THÔNG TIN VÀ TRUYỀN THÔNG

School of Inform ation and Com m unication Technology

12
 Select Query

• SELECT statement is used to fetch the data from a database

table which returns this data in the form of a result table
• Syntax
SELECT column1, column2, columnN FROM table_name;

SELECT ID, NAME, SALARY FROM SELECT * FROM CUSTOMERS;

CUSTOMERS;

TRƯ Ờ NG CÔNG NGHỆ THÔNG TIN VÀ TRUYỀN THÔNG

School of Inform ation and Com m unication Technology

13
 Where Clause

• WHERE clause is used to specify a

condition while fetching the data
from a single table or by joining
with multiple tables
• Syntax
SELECT column1,.., columnN
FROM table_name
WHERE [condition]
• Operators: >, <, =, LIKE, NOT, etc.
• Example
SELECT ID, NAME, SALARY
FROM CUSTOMERS
WHERE SALARY > 2000;

TRƯ Ờ NG CÔNG NGHỆ THÔNG TIN VÀ TRUYỀN THÔNG

School of Inform ation and Com m unication Technology

14
 AND and OR Conjunctive Operators

• AND & OR operators are used to

combine multiple conditions to
narrow data in an SQL statement.
• Syntax
SELECT column1, ,columnN
FROM table_name
WHERE [condition1] AND...AND
[conditionN];
• Example
SELECT ID, NAME, SALARY
FROM CUSTOMERS
WHERE SALARY > 2000 AND age <
25;

TRƯ Ờ NG CÔNG NGHỆ THÔNG TIN VÀ TRUYỀN THÔNG

School of Inform ation and Com m unication Technology

15
Update Query

• UPDATE Query is used to modify the existing records in

a table.
• Syntax
UPDATE table_name
SET column1 = value1, column2 = value2....,
columnN = valueN
WHERE [condition];
• Example:
UPDATE CUSTOMERS
SET ADDRESS = 'Pune’
WHERE ID = 6;

TRƯ Ờ NG CÔNG NGHỆ THÔNG TIN VÀ TRUYỀN THÔNG

School of Inform ation and Com m unication Technology

16
 Delete Query

• DELETE Query is used to delete the existing records

from a table.
• Syntax
DELETE FROM table_name
WHERE [condition];
• Example
DELETE FROM CUSTOMERS
WHERE ID = 6;

TRƯ Ờ NG CÔNG NGHỆ THÔNG TIN VÀ TRUYỀN THÔNG

School of Inform ation and Com m unication Technology

17
Like Clause

• LIKE clause is used to compare a

string to similar values using
wildcard operators.
• %: zero, one or multiple
characters
• _: one character
• Example syntax
SELECT column FROM
table_name
WHERE column LIKE
‘_XXXX%’
• Example
SELECT * FROM CUSTOMERS
WHERE NAME LIKE ‘%k%’

TRƯ Ờ NG CÔNG NGHỆ THÔNG TIN VÀ TRUYỀN THÔNG

School of Inform ation and Com m unication Technology

18
 LIMIT

• LIMIT clause is used to restrict the

number of rows returned by a query
• Syntax
SELECT column_name(s)
FROM table_name
WHERE [condition]
LIMIT number;
• Example
SELECT * FROM CUSTOMERS
LIMIT 3;

TRƯ Ờ NG CÔNG NGHỆ THÔNG TIN VÀ TRUYỀN THÔNG

School of Inform ation and Com m unication Technology

19
 Order By

• ORDER BY clause is used to sort the

data in ascending or descending order,
based on one or more columns. Some
databases sort the results in an
ascending order by default.
• Syntax
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2,
.. columnN] [ASC | DESC];
• Example:
SELECT * FROM CUSTOMERS
ORDER BY NAME DESC;

TRƯ Ờ NG CÔNG NGHỆ THÔNG TIN VÀ TRUYỀN THÔNG

School of Inform ation and Com m unication Technology

20
 Group By

• GROUP BY clause is used in

collaboration with the SELECT statement
to arrange identical data into groups.
• Syntax
SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
• Example:
SELECT ID,NAME, SUM(SALARY)
FROM CUSTOMERS
GROUP BY ID, NAME;

TRƯ Ờ NG CÔNG NGHỆ THÔNG TIN VÀ TRUYỀN THÔNG

School of Inform ation and Com m unication Technology

21
 Distinct

• DISTINCT keyword is used in conjunction

with the SELECT statement to eliminate all
the duplicate records and fetching only
unique records.
• Syntax
SELECT DISTINCT
column1,...columnN
FROM table_name
WHERE [condition]
• Example:
SELECT DISTINCT SALARY
FROM CUSTOMERS
ORDER BY SALARY;

TRƯ Ờ NG CÔNG NGHỆ THÔNG TIN VÀ TRUYỀN THÔNG

School of Inform ation and Com m unication Technology

22
Excercise

• Give a relational database containing some tables:

• student(sid, firstname, lastname, address, city, dob, gender)
• course(cid, name, credit, weight)
• Write SQL queries to perform following requirements:
1. Define the database and tables above.
2. Insert data into tables. Each table should contain at least 10 rows
Note: default format of date value is ‘YYYY-MM-DD’ e.g., ‘2010-10-
10’

TRƯ Ờ NG CÔNG NGHỆ THÔNG TIN VÀ TRUYỀN THÔNG

School of Inform ation and Com m unication Technology

23