HARAR TEACHERS AND BUSINESS EDUCATION COLLEGE
DEPARTMENT OF COMPUTER SCIENCE
Course: Fundamental of database
By Inst:Anteneh (MscIT)
SQL command Lab Practice
CREATE Command in SQL
SQL Create the database or its object (ie table, index, view, function, etc.).
Syntax
CREATE DATABASE databasename
Example
CREATE DATABASE Student_data;
Syntax
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
);
Example
CREATE TABLE Student (
StudendId int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
Mark int
);
DROP Command in SQL
Drop command helps to delete the object from the database (ie table,
index, view, function, etc.).
Syntax
DROP object object_name
Example
DROP TABLE Student;
Syntax
DROP DATABASE database_name
Example
DROP DATABASE Student_data;
ALTER Command in SQL
Alter command is helpful to change or modify the structure of the database
or its object.
Syntax
ALTER TABLE table_name
ADD column_name datatype
Example
ALTER TABLE Student
ADD Total int;
SQL
Syntax
ALTER TABLE table_name
DROP COLUMN column_name
Example
ALTER TABLE Student
DROP COLUMN Mark;
1) SQL Server / MS Access
Syntax
ALTER TABLE table_name
ALTER COLUMN column_name datatype
Example
ALTER TABLE Student
ALTER COLUMN Total Varchar(255);
2) My SQL / Oracle (prior version 10G)
Syntax
ALTER TABLE table_name
MODIFY COLUMN column_name datatype
Example
ALTER TABLE Student
MODIFY COLUMN Total Varchar(255);
3) Oracle 10G and later
Syntax
ALTER TABLE table_name
MODIFY column_name datatype
Example
ALTER TABLE Student
MODIFY Total Varchar(255);
TRUNCATE Command in SQL
SQL Truncate command helps to remove all records from a table.
Syntax
TRUNCATE TABLE table_name
Example
TRUNCATE TABLE Student;
COMMENT Command in SQL
SQL Comment is helpful to add comments to the data dictionary."--" is
used to comment on the notes.
Syntax
--(notes,examples)
Example
--select the student data
SELECT * FROM Student;
SELECT Command in SQL
SQL SELECT a query on a table or tables to view the temporary table
output from the database.
Syntax
Select * from Table_Name;
Example
Select * from Student;
DML(Data Manipulation Language) Command in
SQL
DML or Data Manipulation Language is to manipulate the data inside the
database. With the help of DML commands, we can insert, delete, and
change the data inside the database. Find more about DML Command in
SQL: DML Command in SQL.
INSERT Command in SQL
SQL Insert command is helpful to insert the data into a table.
1) All the column names are mentioned in the insert statement.
Syntax
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...)
Example
INSERT INTO Student (StudendId, FirstName, LastName)
VALUES (12345, "Sri", "Durga");
2) Column names do not need to mention in the query, Values should be
given in the order according to the column.
Syntax
INSERT INTO table_name
VALUES (value1, value2, value3, ...)
Example
INSERT INTO Student
VALUES (12345, "Sri", "Durga");
UPDATE Command in SQL
SQL Update command is helpful to update the existing data in a table.
Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition
Example
UPDATE Student
SET FirstName = "Navin" , LastName = "Kumar"
WHERE StudentId=12345;
DELETE Command in SQL
SQL Delete command helps to delete the records from a database table.
Syntax
DELETE FROM table_name WHERE condition;
Example
DELETE FROM Student WHERE StudentId=12345;
LOCK Command in SQL
SQL Lock command is helpful to lock the table to control concurrency.
Syntax
LOCK TABLE table-Name IN { SHARE | EXCLUSIVE } MODE
Example
LOCK TABLE Student IN SHARE MODE;
CALL Command in SQL
SQL Call command is helping to Call a PL/SQL or JAVA subprogram.
Syntax
EXEC SQL
CALL GETEMPSVR (2, NULL)
END-EXEC
EXPLAIN PLAN
It describes the access path to the data.
Syntax
EXPLAIN PLAN FOR
SELECT Column_name FROM table_name
Example
EXPLAIN PLAN FOR
SELECT last_name FROM Student;
This query explanation will be stored in the PLAN_TABLE table. We can
then select the execution plan to review the queries.
DCL (Data Control Language) Command in SQL
DCL or Data Control Language is to provide rights, permissions, and other
controls of the database system. Find more about DCL Command: DCL
Command in SQL.
GRANT Command in SQL
GRANT command is helpful to provide privileges to the database.
Syntax
GRANT privileges_names ON object TO user
Example
GRANT SELECT, INSERT, DELETE, UPDATE ON Users TO
'Name'@'localhost;
GRANT ALL ON Users TO 'Name'@'localhost;
GRANT SELECT ON Users TO '*'@'localhost;
Syntax
GRANT EXECUTE ON [ PROCEDURE | FUNCTION ] object TO user
REVOKE Command in SQL
SQL Revoke command is to withdraw the user’s access privileges given by
using the GRANT command.
Syntax
REVOKE privileges ON object FROM user
Example
REVOKE SELECT, INSERT, DELETE, UPDATE ON Users TO
'Name'@'localhost;
REVOKE ALL ON Users TO 'Name'@'localhost;
REVOKE SELECT ON Users TO '*'@'localhost;
Syntax
REVOKE EXECUTE ON [ PROCEDURE | FUNCTION ] object FROM user
TCL (Transaction Control Language) Command in
SQL
TCL or Transaction Control Language happens to a transaction in the
database.
COMMIT Command in SQL
SQL Commit command is to commit the Transaction after insert or delete in
the database.
Syntax
Commit;
Example
DELETE from Student where Student_Id = 12345;
COMMIT;
ROLLBACK Command in SQL
SQL Rollback command is to rollback a transaction in case of any error
occurs.
Syntax
Rollback;
Example
DELETE from Student where Student_Id = 12345;
ROLLBACK;