Download DBMS practical soft copy and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!
Object 1: Write a query using DDL commands.
CREATE Command: It is used to create the database or it
objects (like table, index, function, views and triggers).
Syntax: CREATE user user_name identified by password;
CREATE table table_name;
CREATE user university identified by 12345;
CREATE table student(Name varchar(10),Branch varchar(10),
roll_no int,Address varchar(10));
Column Name Data Type Nullable Default Primary Key NAME VARCHAR2(10) Yes BRANCH VARCHAR2(10) Yes ROLL_NO NUMBER Yes ADDRESS VARCHAR2(10) Yes
ALTER Command : It is used to alter the structure of the
database like add,drop,modify and rename the column from table.
Syntax: ALTER table table_name add column_name datatype;
Ex: ALTER table student add marks float;
ALTER table student modify name varchar(30);
Column Name Data Type Nullable Default Primary Key NAME VARCHAR2(30) Yes BRANCH VARCHAR2(10) Yes ROLL_NO NUMBER Yes ADDRESS VARCHAR2(20) Yes MARKS FLOAT Yes
RENAME Command : It is used to change the column name from
old column name to new column name.
Syntax: ALTER table table_name RENAME column
old_column_name to new_column_name;
Ex: ALTER table student RENAME Name to Stu_Name;
Column Name Data Type Nullable Default Primary Key STU_NAME VARCHAR2(30) Yes BRANCH VARCHAR2(10) Yes ROLL_NO NUMBER Yes ADDRESS VARCHAR2(20) Yes MARKS FLOAT Yes
TRUNCATE Command : It is used to delete complete data from
an existing table.
Syntax: TRUNCATE table table_name;
Ex: TRUNCATE table student;
Column Name Data Type Nullable Default Primary Key STU_NAME VARCHAR2(30) Yes BRANCH VARCHAR2(10) Yes ROLL_NO NUMBER Yes ADDRESS VARCHAR2(20) Yes MARKS FLOAT Yes
DROP Command : It is used to remove the database and the
structure of the existing table with data which we want to delete,
so that the existing table is no more access by the user.
Syntax: DROP table table_name;
Object 2: Write a query using DML commands.
INSERT Command : Insert statement is used to add new rows of
data to a table in the database.
Ex: DELETE from student WHERE BRANCH=’Ece’;
NAME BRANCH ROLL_NO ADDRESS MARKS
sakshi cse 2018022019 delhi 1000 kajal ECE 2018022008 sultanpur 2000 arti CHE 2018022005 mumbai 4000 shivangi EE 2018022054 punjab 8000
UPDATE Command : UPDATE query is used to modify the
existing records in a table.you can use the WHERE clause with
the UPDATE query to update the selected rows.
Syntax: UPDATE table_name SET
column1=value1,column2=value2,….columnN=valueN WHERE
[condition];
NAME BRANCH ROLL_NO ADDRESS MARKS
sakshi cse 2018022019 delhi 1000 kajal ECE 2018022008 sultanpur 2000 diksha civil 2018022005 mumbai 4000 shivangi EE 2018022054 punjab 8000
Object 3: Write a query using TCL commands.
COMMIT Command : The COMMIT command is the transactional
command used to save changes invoked by a transaction to the
database.
Syntax: COMMIT;
Ex:Delete from student where marks=500;
commit;
NAME BRANCH ROLL_NO ADDRESS MARKS
sakshi cse 2018022019 delhi 1000 kajal ECE 2018022008 sultanpur 2000 diksha civil 2018022005 mumbai 4000 shivangi EE 2018022054 punjab 8000
ROLLBACK Command : The ROLLBACK command is the
transactional command used to undo transactions that have not
already been saved to the database.
Syntax: ROLLBACK;
Ex:Delete from student where marks=500;
NAME BRANCH ROLL_NO ADDRESS MARKS
sakshi cse 2018022019 delhi 1000 kajal ECE 2018022008 sultanpur 2000 diksha civil 2018022005 mumbai 4000 shivangi EE 2018022054 punjab 8000
ROLLBACK;
NAME BRANCH ROLL_NO ADDRESS MARKS
sakshi CSE 2018022019 delhi 1000 kajal ECE 2018022008 sultanpur 2000 arti CHE 2018022005 mumbai 500 diksha ECE 2018022007 gorakhpu 7000 shivangi EE 2018022054 punjab 8000
- The percent sign(%)
- The underscore(_)
Syntax: SELECT from table_name WHERE column LIKE
‘XXX%’ ;
SELECT from table_name WHERE column LIKE ‘%XXX%’ ;
SELECT from table_name WHERE column LIKE ‘XXX’ ;
Ex: Select * from student WHERE marks LIKE ‘%00’ ;
NAME BRANCH ROLL_NO ADDRESS MARKS
arti CSE 2018022019 delhi 1000 diksha ECE 2018022008 sultanpur 2000 kajal CHE 2018022005 mumbai 500 sakshi ECE 2018022007 gorakhpu 7000 shivangi EE 2018022054 punjab 8000
Select * from student WHERE marks LIKE ‘_00%’ ;
NAME BRANCH ROLL_NO ADDRESS MARKS
sakshi cse 2018022019 delhi 1000 kajal ECE 2018022008 sultanpur 2000 diksha civil 2018022005 mumbai 4000 shivangi EE 2018022054 punjab 8000
IN Clause : The SQL IN condition allows you to easily test if an
expression matches any value in a list of values. It is used to help
reduce the need for multiple OR condition in a SELECT, INSERT,
UPDATE , or DELETE statement.
Syntax: SELECT * from table_name WHERE column_name
IN( value1,value2,…..valueN) ;
Ex: Select * from student WHERE branch IN(‘cse’,’ECE’,’civil’) ;
NAME BRANCH ROLL_NO ADDRESS MARKS
sakshi cse 2018022019 delhi 1000 kajal ECE 2018022008 sultanpur 2000 diksha civil 2018022005 mumbai 4000
BETWEEN Clause : The BETWEEN operator selects values
within a given range.The values can be numbers ,text,or dates.
The BETWEEN operator is inclusive: begin and end values are
included.
Syntax: SELECT column_name FROM table_name WHERE
column_name BETWEEN value1 AND valu2;
Ex: Select * from student WHERE marks BETWEEN 2000 AND
NAME BRANCH ROLL_NO ADDRESS MARKS
sakshi cse 2018022019 delhi 1000 kajal ECE 2018022008 sultanpur 2000 diksha civil 2018022005 mumbai 4000
Object 5: Write a query for GROUP BY , ORDER BY
and HAVING Clause.
GROUP BY Clause: GROUP BY clause is used in collaboration
with the SELECT statement to arrange identical data into groups.
Ex: Select * from student GROUP BY marks HAVING
COUNT(marks)>=2;
NAME BRANCH ROLL_NO ADDRESS MARKS
arti CSE 2018022019 delhi 2000
Object 6: Write a query for String Function.
SQL string functions are used primarily for string manipulation.
LOWER(str) : Returns the string str with all characters changed
to lowercase according to the current character set mapping.
Syntax: SELECT LOWER(‘SHIVANGI’) from table_name;
Ex: Select LOWER(‘SHIVANGI’) from student;
Output: shivangi
UPPER(str) : Returns the string str with all characters changed to
uppercase according to the current character set mapping.
Syntax: SELECT UPPER(‘shivangi’) from table_name;
Ex: Select UPPER(‘shivangi’) from student;
Output: SHIVANGI
TRIM(str) : Returns the string str with all prefixes or suffixes
removed.it removed spaces.
Syntax: SELECT TRIM(‘ shivangi ’) from table_name;
Ex: Select TRIM(‘ shivangi ’) from student;
Output: shivangi
LTRIM(str) : Returns the string str with leading space characters
removed.
Syntax: SELECT LTRIM(‘ shivangi’) from table_name;
Ex: Select LTRIM(‘ shivangi’) from student;
Output: shivangi
RTRIM(str) : Returns the string str with leading space characters
removed.
Syntax: SELECT RTRIM(‘shivangi ’) from table_name;
Ex: Select RTRIM(‘shivangi ’) from student;
Output: shivangi
LEFT(str,len) : Returns the leftmost len characters from the string
str.
Syntax: SELECT LEFT(‘shivangi’,3) from table_name;
Ex: Select LEFT(‘shivangi’,3) from student;
Output: shi
Ex: Select SUM(marks) from student;
MAX Function : This MAX function is used to find maximum
value in the column that is supplied as a parameter. It can be
used on any type of data.
Syntax: SELECT MAX(*) from table_name;
Ex: Select MAX(marks) from student;
NAME BRANCH ROLL_NO ADDRESS MARKS
arti CSE 2018022019 delhi 500000
MIN Function : This MIN function is used to find minimum value
in the column that is supplied as a parameter. It can be used on
any type of data.
Syntax: SELECT MIN(*) from table_name;
Ex: Select MIN(marks) from student;
NAME BRANCH ROLL_NO ADDRESS MARKS
kajal CSE 2018022019 delhi 5000
