SQL

UNIT-3
 Table of Content
 DBMS v/s RDBMS
 Introduction to SQL: Data types, Constraints
 Commands in SQL: Create table, Drop command, Alter Queries in SQL
 Statements in SQL (Insert, delete and update)
 Features of SQL
 Manipulation of data
 Tables in SQL
 Conclusion of the Unit
 DBMS vs RDBMS
 Although DBMS and RDBMS both are used to store information in physical database but
there are some remarkable differences between them.
No. DBMS RDBMS
1) DBMS applications store data as file. RDBMS applications store data in a tabular
form.
2) In DBMS, data is generally stored in In RDBMS, the tables have an identifier
either a hierarchical form or a called primary key and the data values are
navigational form. stored in the form of tables.
3) Normalization is not present in DBMS. Normalization is present in RDBMS.

4) DBMS does not apply any security with RDBMS defines the integrity constraint for
regards to data manipulation. the purpose of ACID (Atomocity,
Consistency, Isolation and Durability)
property.
5) DBMS uses file system to store data, so in RDBMS, data values are stored in the
there will be no relation between the form of tables, so a relationship between
tables. these data values will be stored in the form
of a table as well.
6) DBMS has to provide some uniform RDBMS system supports a tabular structure
methods to access the stored information. of the data and a relationship between them
to access the stored information.

7) DBMS does not support distributed RDBMS supports distributed database.

database.
8) DBMS is meant to be for small RDBMS is designed to handle large
organization and deal with small data. it amount of data. it supports multiple users.
supports single user.
9) Examples of DBMS are file Example of RDBMS
systems, xml etc. are mysql, postgre, sql server, oracle etc.
 Introduction to SQL: Data types
 Data types are used to represent the nature of the data that can be stored in the
database table. For example, in a particular column of a table, if we want to store a
string type of data then we will have to declare a string data type of this column.
 Data types mainly classified into three categories for every database.
• String Data types
• Numeric Data types
• Date and time Data types
 Constraints
 Constraints in SQL means we are applying certain conditions or restrictions on the
database. This further means that before inserting data into the database, we are
checking for some conditions. If the condition we have applied to the database holds
true for the data which is to be inserted, then only the data will be inserted into the
database tables.
 Constraints in SQL can be categorized into two types:
1. Column Level Constraint:
Column Level Constraint is used to apply a constraint on a single column.
2. Table Level Constraint:
Table Level Constraint is used to apply a constraint on multiple columns.
Constraints available in SQL are:
1. NOT NULL
• NULL means empty, i.e., the value is not available.
• Whenever a table's column is declared as NOT NULL, then the value for that column
cannot be empty for any of the table's records.
• There must exist a value in the column to which the NOT NULL constraint is applied.
NOTE: NULL does not mean zero. NULL means empty column, not even zero.
2. UNIQUE
• Duplicate values are not allowed in the columns to which the UNIQUE constraint is
applied.
• The column with the unique constraint will always contain a unique value.
• This constraint can be applied to one or more than one column of a table, which means
more than one unique constraint can exist on a single table.
• Using the UNIQUE constraint, you can also modify the already created tables.
3. PRIMARY KEY
• PRIMARY KEY Constraint is a combination of NOT NULL and Unique constraints.
• NOT NULL constraint and a UNIQUE constraint together forms a PRIMARY
constraint.
• The column to which we have applied the primary constraint will always contain a
unique value and will not allow null values.

4. FOREIGN KEY
• A foreign key is used for referential integrity.
• When we have two tables, and one table takes reference from another table, i.e., the
same column is present in both the tables and that column acts as a primary key in one
table. That particular column will act as a foreign key in another table.
5. CHECK
• Whenever a check constraint is applied to the table's column, and the user wants to
insert the value in it, then the value will first be checked for certain conditions before
inserting the value into that column.
• For example: if we have an age column in a table, then the user will insert any value of
his choice. The user will also enter even a negative value or any other invalid value.
But, if the user has applied check constraint on the age column with the condition age
greater than 18. Then in such cases, even if a user tries to insert an invalid value such as
zero or any other value less than 18, then the age column will not accept that value and
will not allow the user to insert it due to the application of check constraint on the age
column.
6. DEFAULT
• Whenever a default constraint is applied to the table's column, and the user has not
specified the value to be inserted in it, then the default value which was specified while
applying the default constraint will be inserted into that particular column.
 Commands in SQL
1. 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.
• Let's see the simple syntax to create the table.
create table "tablename"
("column1" "data type", Parameters used in syntax
•table_name: It specifies the name of the
"column2" "data type", table which you want to create.
"column3" "data type", •column1, column2, ... column n: It specifies
... the columns which you want to add in the
table. Every column must have a datatype.
"columnN" "data type");
Oracle CREATE TABLE Example
CREATE TABLE customers
( customer_id number(10),
customer_name varchar2(50),
city varchar2(50)
);
This table contains three columns
• customer_id: It is the first column created as a number datatype (maximum 10 digits
in length) .
• customer_name: it is the second column created as a varchar2 datatype (50
maximum characters in length)
• city: This is the third column created as a varchar2 datatype.
Create table with Column Level Constraints
CREATE TABLE customers
( customer_id number(10) NOT NULL,
customer_name varchar2(50) NOT NULL,
city varchar2(50)
);
Create table with Table Level Constraints
CREATE TABLE customers
( customer_id number(10) NOT NULL,
customer_name varchar2(50) NOT NULL,
city varchar2(50),
CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);
In Oracle Database, create table emp(
Dept is Parent table empno number(4,0),
Emp is Child Table ename varchar2(20),
job varchar2(20),
create table dept ( mgr number(4,0),
deptno number(2,0), hiredate date,
dname varchar2(20), sal number(7,2),
loc varchar2(20), comm number(7,2),
constraint pk_dept primary key(deptno) deptno number(2,0),
); constraint p_k primary key(empno),
constraint fk_deptno foreign key(deptno)
references dept(deptno)
);
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:
DESC customers;

CREATE TABLE AS Statement

The CREATE TABLE AS statement is used to create a table from an existing table by
copying the columns of existing table.
Syntax:
CREATE TABLE new_table AS SELECT * FROM old_table;

Create Table Example: copying all columns of another table

In this example, we are creating a "newcustomers" table by copying all the columns from
the already existing table "Customers".
CREATE TABLE newcustomers AS SELECT * FROM customers WHERE custome
r_id < 5000;
2. ALTER
 In Oracle, ALTER TABLE statement specifies how to add, modify, drop or delete
columns in a table. It is also used to rename a table.

ADD
How to add column in a table
 Syntax:
ALTER TABLE table_name ADD column_name column-definition;
Example:
 Consider that already existing table customers. Now, add a new column customer_age
into the table customers.
ALTER TABLE customers ADD customer_age varchar2(50);
 Now, a new column "customer_age" will be added in customers table.
How to add multiple columns in the existing table
ALTER TABLE customers ADD (customer_type varchar2(50),
customer_address varchar2(50));

MODIFY
How to modify column of a table
ALTER TABLE customers MODIFY customer_name varchar2(100) not null;

How to modify multiple columns of a table

ALTER TABLE customers MODIFY (customer_name varchar2(100) not null, city varc
har2(100));
DROP
How to drop column of a table
Syntax:
ALTER TABLE table_name DROP COLUMN column_name;
Example:
ALTER TABLE customers DROP COLUMN customer_name;

RENAME
How to rename column of a table
Syntax:
ALTER TABLE table_name RENAME COLUMN old_name to new_name;
Example
ALTER TABLE customers RENAME COLUMN customer_name to cname;
3. DROP
Oracle DROP TABLE statement is used to remove or delete a table from the Oracle
database.
Syntax
DROP [schema_name].TABLE table_name [ CASCADE CONSTRAINTS ] [ PURGE ];

Parameters
 schema_name: It specifies the name of the schema that owns the table.
 table_name: It specifies the name of the table which you want to remove from the
Oracle database.
 CASCADE CONSTRAINTS: It is optional. If specified, it will drop all referential
integrity constraints as well.
 PURGE: It is also optional. If specified, the table and its dependent objects are placed
in the recycle bin and can?t be recovered.
DROP TABLE Example
DROP TABLE customers;
This will drop table customers

DROP TABLE Example with PURGE parameter

DROP TABLE customers PURGE
This statement will drop the table called customers and issue a PURGE so that the space
associated with the customers table is released and the customers table is not placed in
recycle bin. So, it is not possible to recover that table if required.
 Statements in SQL (Insert, delete and update)
Insert Statement
 In Oracle, INSERT statement is used to add a single record or multiple records into the
table.
(Inserting a single record using the Values keyword):
Syntax:
INSERT INTO table (column1, column2, ... column_n )
VALUES (expression1, expression2, ... expression_n );

Example
1. INSERT INTO customers(customer_id,customer_name,city) values (101,‘ALLEN’,
‘NEW YORK’)
2. INSERT INTO customers(customer_name,city, customer_id,) values (‘ANDREW’,
‘LONDON’,102)
3. INSERT INTO customers values (103, ‘EMMA’, ‘LONDON’)
4. INSERT INTO customers(customer_id, customer_name,) values (104, ‘JOY’)
5. INSERT INTO customers values (105, ‘JIMMY’)

(Inserting multiple records using a SELECT statement):

Syntax:
INSERT INTO table (column1, column2, ... column_n )
SELECT expression1, expression2, ... expression_n FROM source_table WHERE conditions;
Example
INSERT INTO customer_1 (id,name) SELECT customer_id, customer_name from customers
UPDATE Statement
 In Oracle, UPDATE statement is used to update the existing records in a table.

Syntax:
UPDATE table SET column1 = expression1, column2 = expression2, ... column_n = expr
ession_n WHERE conditions;

Example:
UPDATE customers SET customer_name = ’ANNA' WHERE customer_id = 102;
UPDATE customers SET customer_name = ’ANNA’ , city=‘DALLAS’
WHERE customer_id = 102; updating multiple columns
UPDATE customers SET customer_name = ’ANNA’; changes will be made to all records
DELETE Statement
 In Oracle, DELETE statement is used to remove or delete a single record or multiple
records from a table.
Syntax: DELETE FROM table_name WHERE conditions;
Parameters
1) table_name: It specifies the table which you want to delete.
2) conditions: It specifies the conditions that must met for the records to be deleted.
Oracle Delete Example: On one condition
DELETE FROM customers WHERE customer_name = ’ANDREW';
Oracle Delete Example: On multiple conditions
DELETE FROM customers WHERE customer_name = ’ANNA' AND customer_id > 101;
DELETE all Records of table
DELETE from customers;
 SQL
 SQL stands for Structured Query Language and is a computer language that we use to
interact with a relational database.
 SQL is a tool for organizing, managing, and retrieving archived data from a computer
database.
 The original name was given by IBM as Structured English Query Language,
abbreviated by the acronym SEQUEL.
 When data needs to be retrieved from a database, SQL is used to make the request.
The DBMS processes the SQL query retrieves the requested data and returns it to us.
Rather, SQL statements describe how a collection of data should be organized or what
data should be extracted or added to the database.
 In common usage, SQL encompasses DDL and DML commands for CREATE,
UPDATE, modified, or other operations on database structure.
Uses of SQL
1. Data definition: It is used to define the structure and organization of the stored data
and the relationships among the stored data items.
2. Data retrieval: SQL can also be used for data retrieval.
3. Data manipulation: If the user wants to add new data, remove data, or modifying in
existing data then SQL provides this facility also.
4. Access control: SQL can be used to restrict a user’s ability to retrieve, add, and modify
data, protecting stored data against unauthorized access.
5. Data sharing: SQL is used to coordinate data sharing by concurrent users, ensuring
that changes made by one user do not inadvertently wipe out changes made at nearly
the same time by another user.
 Features of SQL
 Flexibility and Scalability: SQL provides users with flexibility and scalability for relational
database management systems. With SQL, it's easier to create new tables while dropping or
deleting previously-created or seldom-used tables.
 Comprehensive Application Development Tool: Programmers use SQL to program
applications to access a database, making it a comprehensive and effective application
development tool. SQL is suitable for every large or small organization, regardless of size.
 Rich Transactional Support: SQL's programming capability of handling large records while
managing several other transactions is top-notch.
 High Performance: SQL offers high-performance programming capabilities for high usage,
incredibly transactional, and heavy workload database systems. SQL's programming provides
different ways to describe data more analytically.
 High Availability: SQL is compatible with other databases such as Microsoft SQL Server,
Oracle Database, MS Access, MySQL, SAP Adaptive Server, and more. These RDBMSs
support SQL, and it's easier to create application extensions for procedural programming
and several other SQL functions that are extra features, hence making SQL a strong tool.
 High Security: SQL also has high security as one of its notable features. It's easy to give
permissions on views, procedures, and tables. So with SQL, you get optimum security for
your data.
 SQL's Management Ease: Almost every Relational Database Management System uses
Structured Query Language. These commands help users manage large amounts of data
from a database efficiently and quickly.
 Manipulation of data
 The SQL data manipulation language (DML) is used to query and modify database
data. In this chapter, we will describe how to use the SELECT, INSERT, UPDATE,
and DELETE SQL DML command statements, defined below.
 SELECT – to query data in the database
 INSERT – to insert data into a table
 UPDATE – to update data in a table
 DELETE – to delete data from a table
 Tables in SQL
Create table, Alter Table, Drop Table (Commands in SQL, Slide Number 11-19)

Rename Table
RENAME TABLE allows you to rename an existing table in any schema (except the
schema SYS).
 To rename a table, you must either be the database owner or the table owner.
Syntax: RENAME TABLE table-Name TO new-Table-Name
 If there is a view or foreign key that references the table, attempts to rename it will
generate an error. In addition, if there are any check constraints or triggers on the table,
attempts to rename it will also generate an error.
Example : RENAME departments_new TO emp_departments;
Truncate Table
 In Oracle, TRUNCATE TABLE statement is used to remove all records from a table. It
works same as DELETE statement but without specifying a WHERE clause.
 Once a table is truncated, it can’t be rolled back. The TRUNCATE TABLE statement
does not affect any of the table’s indexes, triggers or dependencies.
Syntax: TRUNCATE TABLE table_name

Parameters
1) table_name: It specifies the table that you want to truncate.

Example
TRUNCATE TABLE customers;
Thankyou