Database Management
Systems
LAB – 1 & 2
By
Dr. Priyambada Subudhi
Assistant Professor
SQL
■ SQL is Structured Query Language, which is a computer language for storing, manipulating and
retrieving data stored in a relational database.
■ SQL is the ANSI/ISO standard language for Relational Database Management Systems (RDBMS).
■ RDBMS: A database management system that manages data as a collection of tables. This table is
basically a collection of related data entries and it consists of numerous columns and rows.
■ Although, there are different versions of the SQL language (T-SQL, PL/SQL, JET SQL) , however,
to be compliant with the ANSI standard, they all support at least the major commands.
■ All RDBMS like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as
their standard database language.
History of SQL
▪ 1970–E. F. Codd develops relational database concept
▪ 1974-1979–System R with Sequel (later SQL) created at IBM
Research Lab
▪ 1979–Oracle markets first relational DB with SQL
▪ 1981 – SQL/DS first available RDBMS system on DOS/VSE
▪ Others followed: INGRES (1981), IDM (1982), DG/SGL (1984), Sybase
(1986)
▪ 1986–ANSI SQL standard released
▪ 1989, 1992, 1999, 2003, 2006, 2008–Major ANSI standard updates
▪ Current–SQL is supported by most major database vendors
SQL Commands
■The standard SQL commands to interact with relational databases can
be classified into the following groups based on their nature
■ DDL - Data Definition Language: Commands that define a database, including
creating, altering, and dropping tables and establishing constraints.
Sr.No. Command & Description
CREATE
1
Creates a new table, a view of a table, or other object in the database.
ALTER
2
Modifies an existing database object, such as a table.
DROP
3 Deletes an entire table, a view of a table or other objects in the
database.
• DML - Data Manipulation Language: Commands that maintain and query
a database.
Sr.No. Command & Description
SELECT
1
Retrieves certain records from one or more tables.
INSERT
2
Creates a record.
UPDATE
3
Modifies records.
DELETE
4
Deletes records.
Sample SQL Datatypes
Create Table Construct
▪ An SQL relation is defined using the create table command:
▪ CREATE TABLE table_name (
column1 datatype,
…….
columnN datatype,
(integrity-constraint1),
...,
(integrity-constraintk));
▪ table_name is the name of the table
▪ each columnI is an attribute name in the table table_name
▪ datatype is the data type of values in the domain of attribute columnI
▪ Example:
CREATE TABLE Instructor (
ID int,
name varchar2(30),
dept_name varchar2(20),
salary int);
Describe structure of a Table
• The structure of a table can be viewed using DESCRIBE or DESC command.
• Syntax: DESCRIBE table_name;
or
DESC table_name;
• Example: DESC Instructor;
Name Null Type
ID int
name varchar2(30)
dept_name varchar2(20)
salary int
Insert Value intoTable
• Values can be inserted into a table using INSERT command.
• Syntax:
INSERT INTO table_name (column1, column2, …..columnN)
VALUES (value1, value2, …..valueN);
• Example:
INSERT INTO Instructor (ID, name, dept_name, salary)
VALUES (10101, ‘Srinivasan’, ‘Comp. Sci.’, 65000);
SELECT Statement
• The SQL 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;
• Here, column1, column2... are the fields of a table whose values you
want to fetch.
• If you want to fetch all the fields available in the table, then you can
use the following syntax.
• Syntax:
SELECT* SELECT*
FROM table_name; FROM Instructor;
SQL WHERE Clause
• The WHERE clause is used filter records. It is used to
extract only those records that fulfill a specified condition.
• Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition;
• Example:
SELECT ID, Name
FROM Instructor
WHERE Dept_name=‘Physics’;
Operators in WHERE Clause
SQL ALTER TABLE Statement
• The ALTER TABLE statement is used to add, delete, or modify
columns in an existing table.
• To add a column in a table
Syntax:ALTER TABLE table_name Example: ALTER TABLE Instructor
ADD column_name datatype; ADD Email varchar(50);
• To drop a column in a table
Syntax: ALTER TABLE table_name Example:
ALTER TABLE Instructor
DROP COLUMN column_name; DROP COLUMN Email;
• To modify a column in a table
Syntax: ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
Example: ALTER TABLE Instructor
MODIFY COLUMN Name VARCHAR(50);
SQL UPDATE Statement
• The UPDATE statement is used to modify the existing
records in a table.
• Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
• Example:
UPDATE Instructor
SET Name = 'Alfred'
WHERE ID = 10101;
SQL BETWEEN Operator
• The BETWEEN operator selects values within a given range. It is
inclusive: begin and end values are included.
• Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
• Example:
SELECT * FROM Instructor
WHERE Salary BETWEEN 70000 AND 90000;
SQL LIKE Operator
• The LIKE operator is used in a WHERE clause to search for a specified
pattern in a column.
• There are two wildcards often used in conjunction with the LIKE
operator:
• The percent sign (%) represents zero, one, or multiple characters.
• The underscore sign (_) represents one, single character.
• Syntax: Example:
SELECT column1, column2, ... SELECT * FROM Instructor
FROM table_name WHERE Name LIKE 'a%';
WHERE columnN LIKE pattern;
Some examples showing use of LIKE
operator
SQL IN Operator
• The IN operator allows you to specify multiple values in a WHERE
clause. The IN operator is a shorthand for multiple OR condition.
• Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
• Example:
SELECT * FROM Instructor
WHERE Dept_name IN (‘Physics', ‘Finance', ‘Comp. Sci.');
SQL DELETE Statement
• The DELETE statement is used to delete existing records in a table.
• Syntax:
DELETE FROM table_name WHERE condition;
• Example:
• DELETE FROM Instructor WHERE Name=‘Einstein';
SQL DROP Statement
• DROP table statement is used to drop an existing table in a database.
• Syntax: Example:
DROP TABLE table_name; DROP TABLE Instructor;
