SQL

SQL

• SQL stands for Structured Query Language

• SQL lets you access and manipulate databases
• SQL became a standard of the American National
Standards Institute (ANSI) in 1986, and of the
International Organization for Standardization
(ISO) in 1987
SQL
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and
views
Types of DBMS
languages
Types of DBMS languages:

• Data Definition Language (DDL): DDL is used for

specifying the database schema.
• To create the database instance – CREATE
• To alter the structure of database – ALTER
• To drop database instances – DROP
• To delete tables in a database instance –
TRUNCATE
• To rename database instances – RENAME
• All these commands specify or update the database
schema : Data Definition language.
• Data Manipulation Language (DML): DML is used
for accessing and manipulating data in a database.
• To read records from table(s) – SELECT
• To insert record(s) into the table(s) – INSERT
• Update the data in table(s) – UPDATE
• Delete all the records from the table – DELETE
• Data Control language (DCL): DCL is used for
granting and revoking user access on a database –
• To grant access to user – GRANT
• To revoke access from user – REVOKE
• In practical data definition language, data
manipulation language and data control languages
are not separate language; rather they are the parts
of a single database language such as SQL.
Transaction Control Language:

• It manage the changes made by DML .

• COMMIT-make transaction changes permanent
• ROLLBACK-undo changes made by transaction
either since it started or since save point
• SAVEPOINT-set point to which transaction can be
rolled back
• SET TRANSACTION-establish properties for
transaction
SQL QUERIES-1

DDL-COMMANDS
CREATE
• SQL CREATE TABLE statement is used to create
table in a database.
• If you want to create a table, you should name the
table and define its column and each column's data
type.
• To create the table:
• create table table-name (column-name1 datatype1,
column-name2 datatype2, column-name3
datatype3, column-name4 datatype4 );
create table STUDENTS(id number(10), name
varchar(20), age number(4));
• You can verify it, if you have created the table
successfully by looking at the message displayed by
the SQL Server, else you can use DESC command as
follows:
• SQL> DESC STUDENTS;

Practice:
Create a table Employee having attributes as id,
name, department, age.
Inserting Data into Table

1. Specify both the column names and the

values to be inserted:
INSERT INTO table_name (column1, column2,
column3, ...)
VALUES (value1, value2, value3, ...);
Inserting Data into Table
2. If you are adding values for all the columns of
the table, you do not need to specify the column
names in the SQL query.
However, make sure the order of the values is in
the same order as the columns in the table. Here,
the INSERT INTO syntax would be as follows:

INSERT INTO table_name

VALUES (value1, value2, value3, ...);
Inserting Data into Table

• Insert command is used to insert data into a table.

Following is its general syntax,
• INSERT into table-name values(data1,data2,..);
• Eg: INSERT into STUDENTS values(101, ‘Adam’ ,15);
2.Alter
• alter command is used for alteration of table
structures. There are various uses
of alter command, such as,
• to add a column to an existing table
• To rename any existing column
• To change the datatype of any column or to modify
its size.
• alter is also used to drop a column.
• To Add Column to existing Table
Using alter command we can add a column to an
existing table.
alter table table-name add(column-name datatype);
alter table STUDENTS add(address char);

• The above command will add a new

column address to the Student table
• To Add Multiple Column to existing Table
Using alter command we can even add multiple
columns to an existing table.
Syntax:

alter table table-name add(column-name1

datatype1, column-name2 datatype2, column-name3
datatype3);
Example:

alter table STUDENTS add(father_name varchar(60),

mother_name varchar(60), dob date);

• The above command will add three new columns to

the Student table
• To Modify an existing Column
• alter command is used to modify data type of an
existing column .
alter table table-name modify(column-name
datatype);
• alter table Student modify(address varchar(30));
The above command will modify address column of
the Student table
• To Rename a column
• Using alter command you can rename an existing
column.
alter table table-name rename column old-column-
name to column-name;
alter table Student rename column address to
Location;
• To Drop a Column
• alter command is also used to drop columns.
alter table table-name drop(column-name);

alter table Student drop(address);

Location
• The above command will drop address column from
the Student table
example

CREATE TABLE STUDENTS ( ID number (10) NOT

NULL, NAME VARCHAR (20) NOT NULL, AGE
number NOT NULL, ADDRESS varchar2 (25) );
• It will get that no null value is taken;
Truncate command
• truncate command removes all records from a
table. But this command will not destroy the table's
structure.
truncate table table-name
Example:
truncate table Student;
• The above query will delete all the records
of Student table.
• truncate command is different
from delete command.

delete command will delete all the rows from a table

whereas truncate command re-initializes a table(like a
newly created table).
RENAME query
• rename command is used to rename a table.
• rename old-table-name to new-table-name;
rename Student to Student_record;
• The above query will rename Student table
to Student_record.
DROP command
• drop query completely removes a table from database. This
command will also destroy the table structure. Following is its
Syntax,
• drop table table-name;
• EG:drop table Student;
• The above query will delete the Student table completely.
• It can also be used on Databases. For Example, to drop a
database,
• drop database Test;The above query will drop a database
named Test from the system.
•
SELECT DISTINCT
• The SELECT DISTINCT statement is used to return only
distinct (different) values.
• Inside a table, a column often contains many duplicate values;
and sometimes you only want to list the different (distinct)
values.
Syntax
SELECT DISTINCT column1, column2, ...
FROM table_name;

SELECT DISTINCT Name FROM STUDENTS;

SQL Create Constraints
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);
SQL Constraints
SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a
table. This ensures the accuracy and reliability of the data in the
table. If there is any violation between the constraint and the data
action, the action is aborted.

Constraints can be column level or table level. Column level

constraints apply to a column, and table level constraints apply to
the whole table.
SQL Constraints
The following constraints are commonly used in SQL:
NOT NULL - Ensures that a column cannot have a NULL value

UNIQUE - Ensures that all values in a column are different

PRIMARY KEY - A combination of a NOT NULL and

UNIQUE. Uniquely identifies each row in a table
SQL Constraints
The following constraints are commonly used in SQL:
FOREIGN KEY - Prevents actions that would destroy links
between tables
CHECK - Ensures that the values in a column satisfy a specific
condition
DEFAULT - Sets a default value for a column if no value is
specified
SQL NOT NULL Constraint
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255),
FirstName varchar(255),
Age int
);
SQL Primary Key Constraint
The PRIMARY KEY constraint uniquely identifies each record in
a table.

Primary keys must contain UNIQUE values, and cannot contain

NULL values.

A table can have only ONE primary key;

SQL Primary Key Constraint
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
PRACTICE

• create table ‘YOUR NAME’ with attributes

name,char,reg_no,section and phone_number.
• 1.Add two column in the existing table with
Address and Percentage.
• 2.Change the Reg_No name to ID.
• 3.delete the column address
• 4.rename the table.
• 5.drop the table.
Practice: example

create table Customers (CustomerID int,

CustomerName varchar(150), ContactName
varchar(200), Address City varchar(200), PostalCode
int, Country varchar(200));
Inserting Data into Table
INSERT INTO Customers (CustomerName,
ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21',
'Stavanger', '4006', 'Norway');
MCQ

• In SQL, which command(s) is(are) used to change a

table's storage characteristics?
• A.ALTER TABLE
• B.MODIFY TABLE
• C.CHANGE TABLE
• D.All of the above
• In SQL, which of the following is a data definition
language commands?
• A.RENAME
• B.REVOKE
• C.GRANT
• D.UPDATE
SQL WHERE Clause

• SELECT column1, column2, ...

FROM table_name
WHERE condition;

Example
SELECT * FROM STUDENTS
WHERE name=‘Navjot’;
SQL AND, OR and NOT Operators
1. The AND and OR operators are used to filter records based
on more than one condition.
2. The AND operator displays a record if all the conditions
separated by AND are TRUE.
3. The OR operator displays a record if any of the conditions
separated by OR is TRUE.
4. The NOT operator displays a record if the condition(s) is
NOT TRUE.
SQL AND, OR and NOT Operators
AND Syntax

SELECT column1, column2, ...

FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
SQL AND, OR and NOT Operators
OR Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

NOT Syntax
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
SQL AND, OR and NOT Operators
AND Example
select all fields from "Customers" where country is "Germany"
AND city is "Berlin":

SELECT * FROM Customers

WHERE Country='Germany' AND City='Berlin';
SQL AND, OR and NOT Operators
OR Example
select all fields from "Customers" where city is "Berlin" OR
"Munchen":
SELECT * FROM Customers WHERE City='Berlin' OR
City='München';
select all fields from "Customers" where country is "Germany"
OR "Spain":
SELECT * FROM Customers WHERE Country='Germany' OR
Country='Spain';
SQL AND, OR and NOT Operators
NOT Example
select all fields from "Customers" where country is NOT
"Germany":
SELECT * FROM Customers WHERE NOT Country='Germany';
SQL AND, OR and NOT Operators
Combining AND, OR and NOT
select all fields from "Customers" where country is "Germany"
AND city must be "Berlin" OR "München" (use parenthesis to
form complex expressions)
SELECT * FROM Customers WHERE Country='Germany' AND
(City='Berlin' OR City=‘Munchen’);
SQL AND, OR and NOT Operators
Combining AND, OR and NOT
select all fields from "Customers" where country is NOT
"Germany" and NOT "USA":

SELECT * FROM Customers WHERE NOT Country='Germany'

AND NOT Country='USA';