AMITY SCHOOL OF ENGINEERING

AND TECHNOLOGY

DATABASE MANAGEMENT
SYSTEM

NAME: SUNIDHI
ENROLLMENT NO.: A2305218584
SECTION: 4CSE 9Y
INTRODUCTION TO SQL
SQL is a standard language for accessing and manipulating databases. SQL stands for
Structured Query Language. SQL is used to communicate with a database. According to
ANSI (American National Standards Institute), it is the standard language for relational
database management systems. SQL statements are used to perform tasks such as update data
on a database, or retrieve data from a database.

Some common relational database management systems that use SQL are: Oracle, Sybase,
Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most
of them also have their own additional proprietary extensions that are usually only used on
their system.
However, the standard SQL commands such as "Select", "Insert", "Update", "Delete",
"Create", and "Drop" can be used to accomplish almost everything that one needs to do with
a database.

FUNCTIONS OF SQL
1. SQL can execute queries against a database
2. SQL can retrieve data from a database
3. SQL can insert records in a database
4. SQL can update records in a database
5. SQL can delete records from a database
6. SQL can create new databases
7. SQL can create new tables in a database
8. SQL can create stored procedures in a database
9. SQL can create views in a database
10. SQL can set permissions on tables, procedures, and views

HOW IT WORKS?
A relational database system contains one or more objects called tables. The data or
information for the database are stored in these tables. Tables are uniquely identified by their
names and are comprised of columns and rows. Columns contain the column name, data type,
and any other attributes for the column. Rows contain the records or data for the columns.

A BRIEF HISTORY OF SQL

1970 − Dr. Edgar F. "Ted" Codd of IBM is known as the father of relational
databases. He described a relational model for databases.
1974 − Structured Query Language appeared.
1978 − IBM worked to develop Codd's ideas and released a product named
System/R.
1986 − IBM developed the first prototype of relational database and standardized
by ANSI. The first relational database was released by Relational Software which
later came to be known as Oracle.

APPLICATIONS OF SQL
As mentioned before, SQL is one of the most widely used query language over the
databases. I'm going to list few of them here:
 1. Allows users to access data in the relational database management systems.
2. Allows users to describe the data.
3. Allows users to define the data in a database and manipulate that data.
4. Allows to embed within other languages using SQL modules, libraries & pre-
compilers.
5. Allows users to create and drop databases and tables.
6. Allows users to create view, stored procedure, functions in a database.
7. Allows users to set permissions on tables, procedures and views.

SQL PROCESS
When you are executing an SQL command for any RDBMS, the system determines the best
way to carry out your request and SQL engine figures out how to interpret the task. There are
various components included in this process.
These components are −
1. Query Dispatcher
2. Optimization Engines
3. Classic Query Engine
4. SQL Query Engine, etc.
A classic query engine handles all the non-SQL queries, but a SQL query engine won't handle
logical files.
Following is a simple diagram showing the SQL Architecture –

SQL ARCHITECTURE
 PRACTICAL 1
AIM :- To study DDL commands

DDL COMMANDS (Data definition language)

1. Create
2. Alter
3. Drop

CREATE :- We can use below command to create a database on a SQL Server instance. To
create a database, we must be logged in to the server and have appropriate permissions
(CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE).
Syntax: CREATE DATABASE <database-name>
where <database-name> is the name of the database to be created

ALTER :- We can change the settings of created database using ALTER DATABASE
command. For example, to modify the file name (data or log file), follow below syntax;
Syntax: ALTER DATABASE <database-name>
MODIFY FILE (NAME = <old file name>,NEWNAME = <new file name>)

1. Rename table name :-

Syntax: ALTER TABLE <table name> rename to <new table name>

2. Add column in existing table :-

To add a new column in an existing table.
Syntax: ALTER TABLE <schema-name>.<table-name> ADD <column-name>
<data-type>

3. Rename a column :-
Syntax: ALTER TABLE <table name> rename column <old column name>
to <new column name>

4. To set field not nullable :-

It sets the field or column to not null, means user cannot leave it empty.
Syntax: ALTER TABLE <table name> modify ( <column name> not null)

DROP :- To drop something from the table or to delete something from the
Table.

1. Drop column from table

To remove a column from a table, follow below syntax;
Syntax: ALTER TABLE <schema-name>.<table-name> DROP COLUMN
<column-name>

2. Drop table
To drop a table, follow below syntax;
Syntax: DROP TABLE <schema-name>.<table-name>

DESCRIBING A TABLE :- It shows all the properties of the table.

EXECUTION CODE:
CREATE TABLE Student_details
(name varchar(20), course varchar(50), roll_no int, section varchar(5),DOB
varchar(10),address varchar(50)

DESCRIBE Student_details

ALTER TABLE Student_details

ADD semester int

ALTER TABLE Student_details

MODIFY course varchar(20) NOT NULL
ALTER TABLE student_details
DROP COLUMN DOB

ALTER TABLE student_details

RENAME COLUMN section TO sec
I
 PRACTICAL 2
AIM:- To study DML Commands

Data Manipulation Language (DML) statements or commands are used for managing data
within tables. Some commands of DML are:
Some commands of DML are:
1. SELECT – retrieve data from the a database
2. INSERT – insert data into a table
3. UPDATE – updates existing data within a table
4. DELETE – deletes all records from a table, the space for the records remain

INSERT :
The insert statement is used to add new row to a table.

Syntax: INSERT INTO <table name> VALUES (<value 1>, ... <value n>)
The inserted values must match the table structure exactly in the number of attributes and the
data type of each attribute. Character type values are always enclosed in single quotes;
number values are never in quotes; date values are often (but not always) in the format ‘yyyy-
mm-dd’ (for example, ‘2006-11- 30’).

UPDATE :
The update statement is used to change values that are already in a table.

Syntax: UPDATE <table name> SET <attribute> = <expression> WHERE <condition>

The update expression can be a constant, any computed value, or even the result of a
SELECT statement that returns a single row and a single column.

DELETE :
The delete statement deletes row(s) from a table.

Syntax: DELETE FROM <table name> WHERE <condition>

If the WHERE clause is omitted, then every row of the table is deleted that matches with the
specified condition.

SELECT :
The SELECT statement is used to form queries for extracting information out of the database.
To select the entire table use *.

Syntax:
For columns: SELECT <attribute>, ….., <attribute n> FROM <table name>
For entire table: SELECT * FROM <table name>
EXECUTION CODE:
CREATE TABLE Student_details
(name varchar(20), course varchar(50), roll_no int, sec varchar(5),address varchar(50),
semester int
)

INSERT INTO student_details

VALUES ( 'VINEET', 'B.TECH CSE', 8545, 'CSE-9', 'GUJARAT', 4)

INSERT INTO student_details

VALUES ( 'SHATAKSHI', 'B.TECH CSE', 8549, 'CSE-1', 'LUCKNOW', 2)

INSERT INTO student_details

VALUES ( 'VARSHA', 'B.TECH CSE', 8579, 'CSE-9', 'MATHURA', 4)

INSERT INTO student_details

VALUES ( 'SUNIDHI', 'B.TECH CSE', 8584, 'CSE-9', 'DEHRADUN', 4)

INSERT INTO student_details

VALUES ( 'SAURABH', 'B.TECH CSE', 8591, 'CSE-9', 'FARIDABAD', 4)

INSERT INTO student_details

VALUES ( 'VANDIT', 'B.TECH CSE', 8603, 'CSE-9', 'MEERUT', 4)

INSERT INTO student_details

VALUES ( 'TUSHAR', 'B.TECH CSE', 8582, 'CSE-9', 'DELHI', 4)

INSERT INTO student_details

VALUES ( 'AKANKSHA', 'B.TECH CSE', 8037, 'CSE-9', 'DELHI', 4)

INSERT INTO student_details

VALUES ( 'MANVI', 'B.TECH CSE', 8675, 'CSE-10', 'JHANSI', 4)

INSERT INTO student_details

VALUES ( 'JANVI', 'B.TECH CSE', 8234, 'CSE-4', 'HARIDWAR', 4)

SELECT * FROM student_details

 SELECT
name
FROM

student_details WHERE roll_no = 8579

UPDATE student_details
SET roll_no = 8578
WHERE name ='VARSHA'

DELETE FROM student_details WHERE roll_no = 8675

UPDATE student_details
SET course = ‘B.SC'
WHERE name ='AKANKSHA'