Practical-1
Aim: Implement SQL queries to perform various DDL Commands. (Create minimum 5 tables with different
data types and operate upon them)
DBMS (Database Management System)
Database management system is a collection of inter-related data and a set of programs to manipulate that data.
E.g. MS SQL Server, Oracle, My SQL, SQLite, MongoDB etc.
Relational DBMS: uses collection of tables to represent both data and relationships among the data.
• SQL stands for Structured Query Language.
• SQL is a standard language for accessing and manipulating databases.
DDL (Data Definition Language)
• It is a set of SQL commands used to create, modify and delete database objects such as tables, views, indices, etc.
DDL commands include:
✓ CREATE: to create objects in a database.
✓ ALTER: to alter the schema, or logical structure, of the database.
✓ DROP: to delete objects from the database.
✓ TRUNCATE: to remove all records from the table.
Oracle supports a set of basic data types.
• There are four basic data type available in SQL
✓ Numerical
✓ Binary
✓ Boolean
✓ Character
✓ Date
Data Definition Language: DDL is the set of commands used to specify the structure of the table
CREATE, ALTER, TRUNCATE, DELETE are examples of DDL commands
CREATE TABLE: This statement is used to create a new table.
Syntax:
CREATE TABLE TABLENAME (COLUMNNAME1 DATATYPE (SIZE), COLUMNNAME2 DATATYPE
(SIZE), ......, COLUMNNAME DATATYPE (SIZE));
DESCRIBE TABLE QUERY: This statement is to used verify whether table has been created according to
Specification.
Syntax:
DESCRIBE tableName;
Adding New Columns: This statement is used to add new columns in any table.
Syntax: DESCRIBE tableName;
Alter Command is used to alter the schema, or logical structure, of the database.
Add New column
This statement is used to add a new column to a table.
Syntax:
ALTER TABLE TABLENAME ADD (NEWCOLUMNNAME DATATYPE1 (SIZE), NEWCOLUMNNAME
DATATYPE2 (SIZE)....);
Modify Column
This statement is used to set newDatatype and newSize as data type and size for specified columns respectively.
Syntax:
ALTER TABLE TABLENAME MODIFY (COLUMNNAME NEWDATATYPE (NEWSIZE));
Drop Columns
This statement is used to delete an existing column from the table along with data held by that column.
Syntax:
ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME;
Rename columns
Syntax: ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
TRUNCATE: to remove all records from the table.
This statement is used to remove all records from a given table.
Note: Records which are deleted using this command cannot be rolled back.
✓ Syntax:
TRUNCATE TABLE TABLENAME;
DROP TABLE: Destroying All Rows
This statement is used to destroy all records along with the structure of the table.
Syntax: DROP TABLE TABLENAME;
Practice Exercise #1:
Create Products(pid, pname, categoryid) table.
CREATE TABLE products ( pid number(3), pname char(20) , categoryid number(10));
Q2: Add data into the table
Method1: INSERT INTO products(pid, pname, categoryid) VALUES (1,'Pear',50);
Method2: INSERT INTO products VALUES (2,'Banana',50);
INSERT INTO products VALUES (3,'Orange',50);
INSERT INTO products VALUES (4,'Apple',50);
INSERT INTO products VALUES (5,'Bread',75);
INSERT INTO products VALUES (6,'butter',25);
INSERT INTO products VALUES (7,'fruit jam',null);
Display records of products table
Select * from products;
Q3:update the pname to 'Grape' for all records whose pname is "Apple".
The following SQL UPDATE statement would perform this update.
UPDATE products SET pname = 'Grapes' WHERE pname = 'Apple';
Display output:
Select * from products;
p pname categoryid
1 Pear 50
2 Banana 50
3 Orange 50
4 Grape 50
5 Bread 75
6 jam NULL
Practice Exercise #2:
Create suppliers table(sid, sname, country)
Update the country to 'India' for all records whose supplier name is "Microsoft".
Insert the following records
sd sname country
100 Microsoft USA
200 Google Sweden
300 Oracle France
400 HP Germany
500 Ford New Zealand
600 JP Morgan Italy
700 IBM Scotland
800 Samsung Korea
900 Cisco USA
Exercise 3
CREATE TABLE employees ( eid int,name char(20), salary number(10));
Insert data into table
101 Rehan 32000
102 Rohit 23000
103 Rakesh19000
104 Rohan 38000
105 Rajesh 59000
Write query to i) delete employee whose eid=102
ii) display name and salary of all employee
iii) alter table employee add column dept varchar2(20);
Practical2
Aim: a. Implement SQL queries to perform various DML Commands. (Insert minimum10 rows using
different insert methods, edit and remove data using update and delete commands) b. Retrieve data using
SELECT command and various SQL operators.
Data Manipulation Language (DML) commands are INSERT, SELECT, UPDATE, DELETE
❖ DML is a set of SQL Commands used to insert, modify and delete data in a database.
❖ These SQL commands are used for storing, retrieving, modifying, and deleting data.
❖ The Data Manipulation Language commands are:
1) INSERT
2) UPDATE
3) DELETE
4) SELECT
1) SQL INSERT Statement
❖ The INSERT Statement is used to add new rows of data to a table.
Syntax:
INSERT INTO TableName(ColumnName1, ColumnName2…ColumnNameN) values
(Expression1, Expression2….ExpressionN);
Example:
INSERT INTO Student (Sno, Sname, age, Branch) values (1,’Niyati’, 17,’CE’);
Output: 1 row inserted
❖ We can insert NULL values in Table Using INSERT Statement.
Example:
INSERT INTO Student (Sno, Sname, age, Branch) values (1,’Niyati’, 17, NULL);
Output: 1 row inserted.
2) SQL UPDATE Statement
❖ The UPDATE Statement is used to modify the existing rows in a table.
Syntax:
UPDATE TableName SET Column_Name1 = value1, Column_Name2 = value2 WHERE condition;
Example:
UPDATE Student SET Name=’nilam’ WHERE Rollno=1;
Output: 1 row Updated.
NOTE:
❖ In the Update statement, the WHERE clause identifies the rows that get affected.
❖ If you do not include the WHERE clause, column values for all the rows get affected.
3) SQL Delete Statement
❖ The DELETE Statement is used to delete rows from a table.
Syntax: DELETE FROM TableName WHERE condition;
Example: ❖ To delete an student with rollno 100 from the student table, the SQL delete query would be
like,
DELETE FROM Student WHERE Rollno = 100;
❖ To delete all the rows from the employee table, the query would be like,
DELETE FROM Student;
4) SQL SELECT Statement
❖ The SELECT command is used to retrieve selected rows from the Tables.
Syntax:
a. SELECT * FROM TableName;
b. SELECT ColumnName1, ColumnName2… ColumnNameN From TableName;
c. SELECT * FROM TableName WHERE Condition;
d. SELECT ColumnName1, ColumnName2… ColumnNameN From TableName WHERE Condition;
Example:
a. SELECT * FROM student;
b. SELECT Rollno, Name FROM student;
c. SELECT * FROM student where Rollno=101;
d. SELECT Rollno, Name FROM student where Rollno=101;
Practical 3:
Aim: Perform queries for TCL and DCL Commands
DCL Commands: Grant and Revoke
GRANT – Granting Privileges
❖ GRANT command is used to grant privileges to give permission to some user to access a database object or a part
of a database object.
❖ This command provides various types of access to database objects such as tables, views and sequences.
Syntax: GRANT object privileges ON object name TO user name [ WITH GRANT OPTION ];
❖ The owner of a database object can grant all privileges or specific privileges to other users.
❖ The WITH GRANT OPTION allows the grantee. User to which privilege is granted to in turn grant object
privilege to other users.
❖ User can grant all or specific privileges owned by him/her.
Example: GRANT ALL ON student TO user2 WITH GRANT OPTION;
Output: Grant Succeeded.
❖ Observe the use of WITH GRANT OPTION. If this option is not specified, user2 will have privileges, but he
cannot grant these privileges to other users.
REVOKE – Revoking Privileges
❖ Revoking privileges means to deny (decline) permission to users given previously.
❖ The owner of an object can revoke privileges granted to another user. A user of the object, who is not an owner,
but has been granted privileges using WITH GRANT OPTION, can revoke the privilege from the grantee.
Syntax: REVOKE object privileges ON object name FROM user name;
Example:
REVOKE SELECT, INSERT ON student FROM user2;
GRANT REVOKE
This DCL command grants permissions This DCL command removes permissions if any
granted to the users on database objects. to the user on the database objects.
It assigns access rights to users. It revokes the user access rights of users.
For each user you need to specify the
permissions. If access for one user is removed; all the
particular permissions provided by that user to
others will be removed.
Transactional Control: Commit, Savepoint, Rollback
❖ TCL – Transaction Control Language
❖ Definition: A Transaction is a set of database operations that performs a particular task.
❖ A transaction must be completely successful or completely fail without doing anything to
maintain database consistency.
❖ We can say that a transaction is considered as a sequence of database operations.
❖ These operations involve various data manipulation operations such as insert, update and delete.
o Changes are permanently saved to the hard disk using COMMIT and can be undone using the ROLLBACK
command.
❖ TCL commands are used to manage transactions, that are given below:
o Commit
o Rollback
o Savepoint
COMMIT: Committing a Transaction
❖ There are two ways to commit a transaction:
o Explicitly
o Implicitly
Explicit Commit:
❖ To commit a transaction explicitly, the user needs to request a COMMIT command explicitly.
❖ A COMMIT command terminates the current transaction and makes all the changes
permanent.
❖ Various data manipulation operations such as insert, update and delete are not effect
permanently until they are committed.
❖ Syntax: COMMIT;
❖ Output: Commit complete
Implicit Commit:
❖ There are some operations which force a COMMIT to occur automatically, even if the user doesn't specify the
COMMIT command.
❖ Some of commands are given below:
Quit Command: To end SQL*PLUS session disconnecting from the Oracle.
Exit Command: To end SQL*PLUS session disconnecting from the Oracle.
Data Definition Language(DDL) commands:
Commands like CREATE.., ALTER, DROP.. are immediate and make all prior changes made during the current
transaction permanent.
ROLLBACK: Canceling a Transaction Completely
❖ A transaction can be canceled using the ROLLBACK command either completely or partially.
❖ A ROLLBACK command terminates the current transaction and undoes any changes made during the transaction.
❖ Oracle also performs auto rollback. In situations like Computer failure, Oracle automatically rollbacks any
uncommitted work, when the database is bought back next time.
SAVEPOINT: Canceling a Transaction Partially
❖ A ROLLBACK command can also be used to terminate the current transaction partially using savepoint.
❖ Syntax: ROLLBACK TO SAVEPOINT savepoint_name;
❖ Output: Rollback Complete.
❖ It is required to create a savepoint to cancel a transaction partially.
❖ A savepoint marks and saves the current point in the processing of a transaction.
❖ A savepoint can be created using command SAVEPOINT as given below:
❖ Syntax: SAVEPOINT savepoint_name;
❖ Output: Savepoint created.
❖ When a ROLLBACK is used with SAVEPOINT, part of the transaction is canceled.
❖ All the operations performed after creating a savepoint are undone.
❖ It is also possible to create more than one savepoint within a single transaction.