DATABASE MANAGEMENT SYSTEMS LAB MANUAL

Experiment 1: Introduction to SQL

AIM: To create alter and dropping of tables and inserting rows into a table (use constraints while creating tables) examples using SELECT command.

SQL (Structured Query Language):
Structured Query Language is a database computer language designed for managing data in relational database management systems(RDBMS), and originally based upon Relational Algebra. Its scope includes data query and update, schema creation and modification, and data access control. SQL was one of the first languages for Edgar F. Codd's relational model in his influential 1970 paper, "A Relational Model of Data for Large Shared Data Banks" and became the most widely used language for relational databases.
  • IBM developed SQL in mid of 1970’s.
  • Oracle incorporated in the year 1979.
  • SQL used by IBM/DB2 and DS Database Systems.
  • SQL adopted as standard language for RDBS by ASNI in 1989.
DATA TYPES:
    1. CHAR (Size): This data type is used to store character strings values of fixed length. The size in brackets determines the number of characters the cell can hold. The maximum number of character is 255 characters.

    1. VARCHAR (Size) / VERCHAR2 (Size): This data type is used to store variable length alphanumeric data. The maximum character can hold is 2000 character.

    1. NUMBER (P, S): The NUMBER data type is used to store number (fixed or floating point). Number of virtually any magnitude may be stored up to 38 digits of precision. Number as large as 9.99 * 10 124. The precision (p) determines the number of places to the right of the decimal. If scale is omitted then the default is zero. If precision is omitted, values are stored with their original precision up to the maximum of 38 digits.

    1. DATE: This data type is used to represent date and time. The standard format is dd-mm-yy as in 17-SEP-2009. To enter dates other than the standard format, use the appropriate functions. Date time stores date in the 24-Hours format. By default the time in a date field is 12:00:00 am, if no time portion is specified. The default date for a date field is the first day the current month.

    1. LONG: This data type is used to store variable length character strings containing up to 2GB. Long data can be used to store arrays of binary data in ASCII format. LONG values cannot be indexed, and the normal character functions such as SUBSTR cannot be applied.

    1. RAW: The RAW data type is used to store binary data, such as digitized picture or image. Data loaded into columns of these data types are stored without any further conversion. RAW data type can have a maximum length of 255 bytes. LONG RAW data type can contain up to 2GB.

interactive SQL:
syntax : verb(Parameter_1,Parameter_2,Parameter_3,........Parameter_n);

SQL language is sub-divided into several language elements, including:
    • Clauses, which are in some cases optional, constituent components of statements and queries.
    • Expressions, which can produce either scalar values or tables consisting of columns and rows of data.
    • Predicates which specify conditions that can be evaluated to SQL three-valued logic (3VL) Boolean truth values and which are used to limit the effects of statements and queries, or to change program flow.
    • Queries which retrieve data based on specific criteria.
    • Statements which may have a persistent effect on schemas and data, or which may control transactions, program flow, connections, sessions, or diagnostics.
    • SQL statements also include the semicolon (";") statement terminator. Though not required on every platform, it is defined as a standard part of the SQL grammar.
    • Insignificant white space is generally ignored in SQL statements and queries, making it easier to format SQL code for readability.
There are five types of SQL statements. They are:
A. data definition LANGUAGE (ddl)
B. data manipulation language (dml)
C. DATA RETRIEVAL LANGUAGE (DRL)
D. TRANSATIONAL CONTROL LANGUAGE (TCL)
E. DATA CONTROL LANGUAGE (DCL)

A. data definition LANGUAGE (ddl): The Data Definition Language (DDL) is used to create and destroy databases and database objects. These commands will primarily be used by database administrators during the setup and removal phases of a database project. Let's take a look at the structure and usage of four basic DDL commands: 
1. CREATE 2. ALTER 3. DROP 4. RENAME

1. CREATE:
(a)create table: This is used to create a new relation and the corresponding
Syntax: create table relation_name (field_1 data_type(Size),field_2 data_type(Size), .. );
Example:
SQL>create table Student (sno NUMBER(3),sname char(10),class char(5));

(b)create TABLE..as select....: This is used to create the structure of a new relation from the structure of an existing relation.
Syntax: create table (relation_name_1, field_1,field_2,.....field_n) AS SELECT field_1,field_2,...........field_n from relation_name_2;
Example: SQL>create table std(rno,sname) as select sno,sname from student;

2. ALTER:
(a)ALTER TABLE ...ADD...: This is used to add some extra fields into existing relation.
Syntax: ALTER TABLE relation_name ADD(new field_1 data_type(size), new field_2 data_type(size),..);
Example : SQL>ALTER TABLE std ADD(Address CHAR(10));

(b)ALTER table...modify...: This is used to change the width as well as data type of fields of existing relations.
Syntax: alter table relation_name modify (field_1 newdata_type(Size), field_2 newdata_type(Size),....field_newdata_type(Size));
Example: SQL>alter table student modify(sname varchar(10),class varchar(5));

3. drop table: This is used to delete the structure of a relation. It permanently deletes the records in the table.
Syntax: drop table relation_name;
Example: SQL>drop table std;

4. Rename: It is used to modify the name of the existing database object.
Syntax: RENAME table old_relation_name TO new_relation_name;
Example: SQL>rename table std to std1;

5. TRUNCATE: This command will remove the data permanently. But structure will not be removed.
Syntax: TRUNCATE TABLE <Table name>
Example TRUNCATE TABLE student;

Difference between Truncate & Delete:-
  • By using truncate command data will be removed permanently & will not get back where as by using delete command data will be removed temporally & get back by using roll back command.
  • By using delete command data will be removed based on the condition where as by using truncate command there is no condition.
  • Truncate is a DDL command & delete is a DML command.

B. data manipulation language (dml): The Data Manipulation Language (DML) is used to retrieve, insert and modify database information. These commands will be used by all database users during the routine operation of the database. Let's take a brief look at the basic DML commands:
1. INSERT 2. UPDATE 3. DELETE

1. insert into: This is used to add records into a relation. These are three type of insert into queries which are as
a) Inserting a single record
Syntax: insert into relationname(field_1,field_2,.field_n)values
(data_1,data_2,........data_n);
Example: SQL>insert into student(sno,sname,class,address)VALUES
(1,’Ravi’,’M.Tech’,’Palakol’);
b) Inserting all records from another relation
Syntax: insert into relation_name_1 select field_1,field_2,field_n
FROM relation_name_2 WHERE field_x=data;
Example: SQL>insert into std select sno,sname from student
where name = ‘Ramu‘;

c) Inserting multiple records
Syntax: insert into relation_name field_1,field_2,.....field_n) values
(&data_1,&data_2,........&data_n);
Example: SQL>insert into student(sno,sname,class,address)
VALUES(&sno,’&sname’,’&class’,’&address’);
Enter value for sno: 101
Enter value for name: Ravi
Enter value for class: M.Tech
Enter value for name: Palakol

2. update-set-WHERE: This is used to update the content of a record in a relation.
Syntax: SQL>update relation name set field_name1=data,field_name2=data,
where field_name=data;
Example: SQL>update student set sname = ‘kumar’ WHERE sno=1;

3. delete-from: This is used to delete all the records of a relation but it will retain the structure of that relation.
a) delete-from: This is used to delete all the records of relation.
Syntax: SQL>delete from relation_name;
example: SQL>delete from std;
b) delete -from-WHERE: This is used to delete a selected record from a relation.
Syntax: SQL>delete from relation_name WHERE condition;
Example: SQL>delete from student WHERE sno = 2;

C. DRL(DATA RETRIEVAL LANGUAGE): Retrieves data from one or more tables.
1. select from: To display all fields for all records.
Syntax : select * from relation_name;
Example : SQL> select * from dept;
DEPTNO DNAME LOC
-------- ----------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

2. Select from: To display a set of fields for all records of relation.
Syntax: select a set of fields FROM relation_name;
Example: SQL> select deptno, dname from dept;
DEPTNO DNAME
------- ----------
10 ACCOUNTING
20 RESEARCH
30 SALES

3. select - from -WHERE: This query is used to display a selected set of fields for a selected set of records of a relation.
Syntax: select a set of fields from relation_name where condition;
Example: SQL> select * FROM dept WHERE deptno<=20;
DEPTNO DNAME LOC
------ ----------- ------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS

4. select - from -group BY: This query is used to group to all the records in a relation together for each and every value of a specific key(s) and then display them for a selected set of fields the relation.
Syntax: select a set of fields FROM relation_name GROUP BY field_name;
Example: SQL> SELECT EMPNO, SUM (SALARY) FROM EMP GROUP BY EMPNO;
EMPNO SUM (SALARY)
------ ----------
1 3000
2 4000
3 5000
4 6000
4 rows selected.

5. select - from -order by: This query is used to display a selected set of fields from a relation in an ordered manner base on some field.
Syntax: select a set of fields FROM relation_name
order by field_name;

Example: SQL> SELECT empno,ename,job FROM emp ORDER BY job;
EMPNO ENAME JOB
------ --------- --------
4 RAVI MANAGER
2 aravind Manager
1 sagar clerk
3 Laki clerk
4rows selected.

6. join using select - from - order by: This query is used to display a set of fields from two relations by matching a common field in them in an ordered manner based on some fields.
Syntax: select a set of fields from both relations from relation_1, relation_2 WHERE relation_1.field_x = relation_2.field_y order by field_z;

Example: SQL>SELECT empno,ename,job,dname FROM emp,dept
WHERE emp.deptno = 20 ORDER BY job;
EMPNO ENAME JOB DNAME
------ ------ ------- ----------
7788 SCOTT ANALYST ACCOUNTING
7902 FORD ANALYST ACCOUNTING
------
7566 JONES MANAGER OPERATIONS
7566 JONES MANAGER SALES
20 rows selected.

7. join using select - from - group by: This query is used to display a set of fields from two relations by matching a common field in them and also group the corresponding records for each and every value of a specified key(s) while displaying.
Syntax: select a set of fields from both relations FROM relation_1,relation_2 WHERE relation_1.field-x=relation_2.field-y group by field-z;
Example: SQL> SELECT empno,SUM(SALARY) FROM emp,dept
WHERE emp.deptno =20 GROUP BY empno;
EMPNO SUM (SALARY)
------- --------
7369 3200
7566 11900
7788 12000
7876 4400

8. union: This query is used to display the combined rows of two different queries, which are having the same structure, without duplicate rows.

Syntax: SELECT field_1,field_2,....... FROM relation_1 WHERE (Condition) UNION SELECT field_1,field_2,....... FROM relation_2 WHERE (Condition);
Example:
SQL> SELECT * FROM STUDENT;
SNO SNAME
----- -------
1 kumar
2 ravi
3 ramu

SQL> SELECT * FROM STD;
SNO SNAME
----- -------
3 ramu
5 lalitha
9 devi
1 kumar

SQL> SELECT * FROM student UNION SELECT * FROM std;
SNO SNAME
---- ------
1 kumar
2 ravi
3 ramu
5 lalitha
9 devi
9. interset: This query is used to display the common rows of two different queries, which are having the same structure, and to display a selected set of fields out of them.
Syntax: select field_1,field_2,.. FROM relation_1 WHERE
(Condition) INTERSECT SELECT field_1,field_2,.. FROM relation_2 WHERE(Condition);
Example : SQL> SELECT * FROM student INTERSECT SELECT * FROM std;
SNO SNAME
---- -------
1 Kumar

10. minus: This query is used to display all the rows in relation_1,which are not having in the relation_2.
Syntax: select field_1,field_2,......FROM relation_1
WHERE(Condition) MINUS SELECT field_1,field_2,.....
FROM relation_2 WHERE(Conditon);
SQL> SELECT * FROM student MINUS SELECT * FROM std;
SNO SNAME
---- -------
2 RAVI
3 RAMU

D. TRANSATIONAL CONTROL LANGUAGE (T.C.L):
A transaction is a logical unit of work. All changes made to the database can be referred to as a transaction. Transaction changes can be mode permanent to the database only if they are committed a transaction begins with an executable SQL statement & ends explicitly with either role back or commit statement.

1. COMMIT: This command is used to end a transaction only with the help of the commit command transaction changes can be made permanent to the database.
Syntax: SQL>COMMIT;
Example: SQL>COMMIT;

2. SAVE POINT: Save points are like marks to divide a very lengthy transaction to smaller once. They are used to identify a point in a transaction to which we can latter role back. Thus, save point is used in conjunction with role back.

Syntax: SQL>SAVE POINT ID;
Example: SQL>SAVE POINT xyz;

3. ROLE BACK: A role back command is used to undo the current transactions. We can role back the entire transaction so that all changes made by SQL statements are undo (or) role back a transaction to a save point so that the SQL statements after the save point are role back.

Syntax: ROLE BACK( current transaction can be role back)
ROLE BACK to save point ID;

Example: SQL>ROLE BACK;
SQL>ROLE BACK TO SAVE POINT xyz;

E. DATA CONTROL LANGUAGE (D.C.L):
DCL provides uses with privilege commands the owner of database objects (tables), has the soul authority ollas them. The owner (data base administrators) can allow other data base uses to access the objects as per their requirement

1. GRANT: The GRANT command allows granting various privileges to other users and allowing them to perform operations with in their privileges
For Example, if a uses is granted as ‘SELECT’ privilege then he/she can only view data but cannot perform any other DML operations on the data base object GRANTED privileges can also be withdrawn by the DBA at any time

Syntax: SQL>GRANT PRIVILEGES on object_name To user_name;
Example: SQL>GRANT SELECT, UPDATE on emp To hemanth;
2. REVOKE: To with draw the privileges that has been GRANTED to a uses, we use the REVOKE command

Syntax: SQL>REVOKE PRIVILEGES ON object-name FROM user_name;
Example: SQL>REVOKE SELECT, UPDATE ON emp FROM ravi;

CONSTRAINTS: There are 5 constraints available in ORACLE:

1. NOT NULL: When a column is defined as NOTNULL, then that column becomes a mandatory column. It implies that a value must be entered into the column if the record is to be accepted for storage in the table.
Syntax:
CREATE TABLE Table_Name(column_name data_type(size) NOT NULL, );
Example:
CREATE TABLE student (sno NUMBER(3)NOT NULL, name CHAR(10));
2. UNIQUE: The purpose of a unique key is to ensure that information in the column(s) is unique i.e. a value entered in column(s) defined in the unique constraint must not be repeated across the column(s). A table may have many unique keys.
Syntax:
CREATE TABLE Table_Name(column_name data_type(size) UNIQUE, ….);
Example:
CREATE TABLE student (sno NUMBER(3) UNIQUE, name CHAR(10));

3. CHECK: Specifies a condition that each row in the table must satisfy. To satisfy the constraint, each row in the table must make the condition either TRUE or unknown (due to a null).
Syntax:
CREATE TABLE Table_Name(column_name data_type(size) CHECK(logical expression), ….);
Example: CREATE TABLE student (sno NUMBER (3), name CHAR(10),class CHAR(5),CHECK(class IN(‘CSE’,’CAD’,’VLSI’));

4. PRIMARY KEY: A field which is used to identify a record uniquely. A column or combination of columns can be created as primary key, which can be used as a reference from other tables. A table contains primary key is known as Master Table.
  • It must uniquely identify each record in a table.
  • It must contain unique values.
  • It cannot be a null field.
  • It cannot be multi port field.
  • It should contain a minimum no. of fields necessary to be called unique.
Syntax:
CREATE TABLE Table_Name(column_name data_type(size) PRIMARY KEY, ….);
Example:
CREATE TABLE faculty (fcode NUMBER(3) PRIMARY KEY, fname CHAR(10));

5. FOREIGN KEY: It is a table level constraint. We cannot add this at column level. To reference any primary key column from other table this constraint can be used. The table in which the foreign key is defined is called a detail table. The table that defines the primary key and is referenced by the foreign key is called the master table.
Syntax: CREATE TABLE Table_Name(column_name data_type(size)
FOREIGN KEY(column_name) REFERENCES table_name);
Example:
CREATE TABLE subject (scode NUMBER (3) PRIMARY KEY,
subname CHAR(10),fcode NUMBER(3),
FOREIGN KEY(fcode) REFERENCE faculty );

Defining integrity constraints in the alter table command:

Syntax: ALTER TABLE Table_Name ADD PRIMARY KEY (column_name);
Example: ALTER TABLE student ADD PRIMARY KEY (sno);
(Or)
Syntax: ALTER TABLE table_name ADD CONSTRAINT constraint_name
PRIMARY KEY(colname)
Example: ALTER TABLE student ADD CONSTRAINT SN PRIMARY KEY(SNO)

Dropping integrity constraints in the alter table command:
Syntax: ALTER TABLE Table_Name DROP constraint_name;
Example: ALTER TABLE student DROP PRIMARY KEY;

(or)

Syntax: ALTER TABLE student DROP CONSTRAINT constraint_name;
Example: ALTER TABLE student DROP CONSTRAINT SN;
Experiment 2: Queries (along with sub Queries)

AIM: To execute queries along with sub queries by using ANY, ALL, IN, EXISTS, NOTEXISTS, UNION, INSERT and Constraints.

Selecting data from sailors table
SQL> select * from sailors;
SID SNAME AGE RATING
--------- ---------- --------- ---------
22 dustin 7 45
29 brutus 1 33
31 lubber 8 55
32 andy 8 25.5
58 rusty 10 35
64 horatio 7 35
71 zorba 10 40
74 horatio 9 40
85 art 3 25.5
95 bob 3 63.5

10 rows selected.
Selecting data from reserves table
SQL> select * from reserves;

SID BID DAY
--------- --------- ------
22 101 10-OCT-98
22 102 10-OCT-98
22 103 10-AUG-98
22 104 10-JUL-98
31 102 11-OCT-98
31 103 11-JUN-98
31 104 11-DEC-98
64 101 09-MAY-98
64 102 09-AUG-98
74 104 09-AUG-98
10 rows selected.
Selecting data from boat table
SQL> select * from boats;
BID BNAME COLOR
--------- -------------------- ----------
101 interlake blue
102 interlake red
103 clipper green
104 marine red

Q: find the names of sailors who have reserved boat 103.
SQL> select s.sname from sailors s where s.sid in (select r.sid from reserves r where r.bid=103);

SNAME
--------------
dustin
lubber
2 rows selected.
Q: find the names of sailors who have reserved a red boat.
SQL> select s.sname from sailors s where s.sid in
(select r.sid from reserves r where r.bid in
(select b.bid from boats b where b.color='red'));
SNAME
--------------------
dustin
lubber
horatio
horatio
4 rows selected.
Q: Find the name and age of the oldest sailors.

SQL> select MAX(s.age)from sailors s;

MAX(S.AGE)
----------
10


Q: Count the number of sailors.
SQL> select COUNT(s.age)from sailors s
COUNT(S.AGE)
------------
10

Q: Count the number of different sailors names.

SQL> select COUNT(distinct s.sname)* from sailors s
COUNT(DISTINCTS.SNAME)
----------------------
9
Q: find the names of sailors who have not reserved a red boat.
SQL> select s.sname
2 from sailors s
3 where s.sid not in (select r.sid
4 from reserves r
5 where r.bid in (select b.bid
6 from boats b
7 where b.color='red'))
SNAME
--------
brutus
andy
rusty
zorba
art
bob
6 rows selected.


Q: find the names of sailors who have not reserved boat 103.
SQL> select s.sname from sailors s where exists(select * from reserves r where r.bid=103 and r.sid=s.sid);



SNAME
-------
dustin
lubber
2 rows selected.
Q: find the names of sailors who have reserved at least one boat.
SQL> select s.sname from sailors s, reserves r where s.sid=r.sid;


SNAME
----------
dustin
dustin
dustin
dustin
lubber
lubber
lubber
horatio
horatio
horatio

10 rows selected.
Q: Compute increments for the ratings of persons who have sailed two different boats on the same day.

SQL> select s.sname,s.rating+1 As rating from sailors s, reserves r1, reserves r2 where s.sid=r1.sid AND s.sid=r2.sid AND r1.day=r2.day AND r1.bid<>r2.bid

SNAME RATING
-------------------- ---------
dustin 46
dustin 46

Q: Find the names of sailors who have reserved a red or a green boat.
SQL> select s.sname from sailors s, reserves r,boats b where s.sid=r.sid AND r.bid=b.bid AND (b.color='red' OR b.color='green')

SNAME
--------------------
dustin
dustin
dustin
lubber
lubber
lubber
horatio
horatio

8 rows selected.
Q: find the all sids of sailors who have rating 10 or have reserved boat 104..
SQL> select s.sid from sailors s where s.rating=10 union
select r.sid from reserves r where r.bid=104;

SID
------
22
31
74
Q: Find the number of reservations for each red boat.
SQL> select b.bid,count(*)As sailorcount from boats b, reserves r where r.bid=b.bid AND b.color='red' group by b.bid;
BID SAILORCOUNT
--------- -----------
102 3
104 3
Q: Find the minimum age of the sailor.

SQL> select min(s.age) from sailors s;

MIN(S.AGE)
----------
1
Q: Find the sum of the rating of sailors.
SQL> select sum(s.rating)from sailors s;
SUM(S.RATING)
-------------
397.5

Q: find the id and names of sailors who have reserved id=22 or age<25.

SQL> select sid,sname from sailors where sid=22 or age<25
SID SNAME
-- --------
22 dustin
Experiment 3: Functions

AIM: To execute Queries using Aggregate functions (COUNT, SUM, AVG, MAX and MIN), GROUP BY, HAVING and Creation and dropping of Views.
PROCEDURE:
Aggregative operators: In addition to simply retrieving data, we often want to perform some computation or summarization. SQL allows the use of arithmetic expressions. We now consider a powerful class of constructs for computing aggregate values such as MIN and SUM.

1. Count: COUNT following by a column name returns the count of tuple in that column. If DISTINCT keyword is used then it will return only the count of unique tuple in the column. Otherwise, it will return count of all the tuples (including duplicates) count (*) indicates all the tuples of the column.
Syntax: COUNT (Column name)
Example: SELECT COUNT (Sal) FROM emp;

2. SUM: SUM followed by a column name returns the sum of all the values in that column.
Syntax: SUM (Column name)
Example: SELECT SUM (Sal) From emp;

3. AVG: AVG followed by a column name returns the average value of that column values.
Syntax: AVG (n1,n2..)
Example: Select AVG(10, 15, 30) FROM DUAL;

4. MAX: MAX followed by a column name returns the maximum value of that column.
Syntax: MAX (Column name)
Example: SELECT MAX (Sal) FROM emp;
SQL> select deptno,max(sal) from emp group by deptno;
DEPTNO MAX(SAL)
------ --------
10 5000
20 3000
30 2850

SQL> select deptno,max(sal) from emp group by deptno having max(sal)<3000;

DEPTNO MAX(SAL)
----- --------
30 2850

5. MIN: MIN followed by column name returns the minimum value of that column.
Syntax: MIN (Column name)
Example: SELECT MIN (Sal) FROM emp;

SQL>select deptno,min(sal) from emp group by deptno having min(sal)>1000;
DEPTNO MIN(SAL)
----- --------
10 1300

VIEW: In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
A view is a virtual table, which consists of a set of columns from one or more tables. It is similar to a table but it doest not store in the database. View is a query stored as an object.
Syntax: create view view_name AS SELECT set of fields FROM relation_name WHERE (Condition)
1. Example:
SQL>create view employee as select empno,ename,job from emp
where job = ‘clerk’;
view created.
sql> select * from employee;
empno ename job
---- ------ -------
7369 smith clerk
7876 adams clerk
7900 james clerk
7934 miller clerk
2.Example:
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products WHERE Discontinued=No

drop view: This query is used to delete a view , which has been already created.
Syntax: drop VIEW view_name;
Example : SQL> DROP VIEW EMPLOYEE;
View dropped
Experiment 4: PL/SQL Conversion functions, String functions, Date functions
AIM: To execute Queries using Conversion functions (to_char, to_number and to_date), string functions (Concatenation, lpad, rpad, ltrim, rtrim, lower, upper, initcap, length, substr and instr), date functions (Sysdate, next_day, add_months, last_day, months_between, least, greatest, trunc, round, to_char, to_date)
PROCEDURE:

1. Conversion functions:
To_char: TO_CHAR (number) converts n to a value of VARCHAR2 data type, using the optional number format fmt. The value n can be of type NUMBER, BINARY_FLOAT, or BINARY_DOUBLE.

SQL>select to_char(65,'RN')from dual;

LXV

To_number : TO_NUMBER converts expr to a value of NUMBER data type.
SQL> Select to_number('1234.64') from Dual;
1234.64

To_date: TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of DATE data type.
SQL>SELECT TO_DATE('January 15, 1989, 11:00 A.M.')FROM DUAL;

TO_DATE('
---------
15-JAN-89

2. String functions:
Concat: CONCAT returns char1 concatenated with char2. Both char1 and char2 can be any of the datatypes 
SQL>SELECT CONCAT(‘ORACLE’,’CORPORATION’)FROM DUAL;
ORACLECORPORATION

Lpad: LPAD returns expr1, left-padded to length n characters with the sequence of characters in expr2.
SQL>SELECT LPAD(‘ORACLE’,15,’*’)FROM DUAL;
*********ORACLE
Rpad: RPAD returns expr1, right-padded to length n characters with expr2, replicated as many times as necessary. 
SQL>SELECT RPAD (‘ORACLE’,15,’*’)FROM DUAL;
ORACLE*********

Ltrim: Returns a character expression after removing leading blanks.
SQL>SELECT LTRIM(‘SSMITHSS’,’S’)FROM DUAL;
MITHSS

Rtrim: Returns a character string after truncating all trailing blanks
SQL>SELECT RTRIM(‘SSMITHSS’,’S’)FROM DUAL;
SSMITH

Lower: Returns a character expression after converting uppercase character data to lowercase.
SQL>SELECT LOWER(‘DBMS’)FROM DUAL;
dbms

Upper: Returns a character expression with lowercase character data converted to uppercase
SQL>SELECT UPPER(‘dbms’)FROM DUAL;
DBMS
Length: Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
SQL>SELECT LENGTH(‘DATABASE’)FROM DUAL;
8
Substr: Returns part of a character, binary, text, or image expression.
SQL>SELECT SUBSTR(‘ABCDEFGHIJ’3,4)FROM DUAL;
CDEF

Instr: The INSTR functions search string for substring. The function returns an integer indicating the position of the character in string that is the first character of this occurrence.
SQL>SELECT INSTR('CORPORATE FLOOR','OR',3,2)FROM DUAL;
14



3. Date functions:
Sysdate:
SQL>SELECT SYSDATE FROM DUAL;
29-DEC-08
next_day:
SQL>SELECT NEXT_DAY(SYSDATE,’WED’)FROM DUAL;
05-JAN-09
add_months:
SQL>SELECT ADD_MONTHS(SYSDATE,2)FROM DUAL;
28-FEB-09
last_day:
SQL>SELECT LAST_DAY(SYSDATE)FROM DUAL;
31-DEC-08
months_between:
SQL>SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE)FROM EMP;
4
Least:
SQL>SELECT LEAST('10-JAN-07','12-OCT-07')FROM DUAL;
10-JAN-07
Greatest:
SQL>SELECT GREATEST('10-JAN-07','12-OCT-07')FROM DUAL;
10-JAN-07
Trunc:
SQL>SELECT TRUNC(SYSDATE,'DAY')FROM DUAL;
28-DEC-08
Round:
SQL>SELECT ROUND(SYSDATE,'DAY')FROM DUAL;
28-DEC-08
to_char:
SQL> select to_char(sysdate, "dd\mm\yy") from dual;
24-mar-05.
to_date:
SQL> select to_date(sysdate, "dd\mm\yy") from dual;
24-mar-o5.
Experiment 5: Stored Procedures and Triggers
AIM: To execute queries using stored procedures
Stored Procedure
A procedure (often called a stored procedure) is a subroutine like a subprogram in a regular computing language, stored in database. A procedure has a name, a parameter list, and SQL statement(s). All most all relational database system supports stored procedure, MySQL 5 introduce stored procedure.

Syntax: CREATE PROCEDURE <procedure_name>
(IN <parameter_name> parameter type,
OUT <parameter_name> parameter type, optional
INOUT <parameter_name> parameter type optional
)
AS
<sql-statement>
GO;
Example:
CREATE PROCEDURE spGetAvgGrade
AS
SELECT AVG(Std_Grade) FROM Students
GO;

Executing a Stored Procedure
Stored procedures can be run by using the EXEC or EXECUTE command. Parameter values can be supplied if a stored procedure is written to accept them.

Syntax: EXEC procedureName optionalProcedureNumber param1=value1| param2=value2
Example:
EXEC spDisplayAll 1;

Altering a stored procedure
In SQL Server, a stored procedure can be modified with the help of the Alter keyword.
Syntax: ALTER PROCEDURE <procedure_name>
(IN <parameter_name>,
OUT <parameter_name>, optional
INOUT <parameter_name> optional
)
AS
<sql-statement>
GO;

Example:
ALTER PROCEDURE spGetAvgGrade (@Course INTEGER)
AS
SELECT AVG(Std_Grade) as AverageGrade FROM Students
WHERE Std_Course = @Course
GO

EXEC spGetAvgGrade 3;

Dropping a Stored Procedure
When a stored procedure is no longer needed, it can be deleted using the DROP PROCEDURE command.
Syntax: DROP PROCEDURE procedureName;
Example: DROP PROCEDURE spSelectStudent1;
Here the stored procedure named spSelectStudent1 is dropped from the database.
Renaming a Stored Procedure:
A stored procedure can be renamed. The new name should follow the rules for identifiers.
Syntax: sp_rename ‘procedure1’, ‘procedure2’
Example: EXEC sp_rename ‘spGetAvgGrade’ , ‘spGetNewAvgGrade’;
The procedure named ‘spGetAvgGrade’ is renamed to spGetNewAvgGrade’.

TRIGGERS
A TRIGGER is a special type of stored procedure, which is 'fired' automatically when the data in a specified table is modified. It is invoked when an INSERT, UPDATE, or DELETE action is performed on a table.
Creating a Trigger: A TRIGGER is created using the CREATE TRIGGER command.
Syntax: CREATE TRIGGER `event_name` BEFORE/AFTER INSERT/UPDATE/DELETE
ON `database`.`table`
FOR EACH ROW BEGIN
-- trigger body
-- this code is applied to every inserted/updated/deleted row
END;
Example1:
DELIMITER $$
CREATE TRIGGER `blog_after_update` AFTER UPDATE
ON `blog`
FOR EACH ROW BEGIN
IF NEW.deleted THEN
SET @changetype = 'DELETE';
ELSE
SET @changetype = 'EDIT';
END IF;
INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype);
END$$
DELIMITER ;

Example2:
delimiter //
CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
IF NEW.amount < 0 THEN
SET NEW.amount = 0;
ELSEIF NEW.amount > 100 THEN
SET NEW.amount = 100;
END IF;
END;//
delimiter ;

Dropping a Trigger: Use the DROP keyword to delete a Trigger. Dropping a table drops all the triggers for that table.
Example: DROP TRIGGER test.ins_sum;
The schema name has to be specified if the trigger is not in the default schema.
Sample Viva Questions


1. What is database or database management systems (DBMS)? and - What’s the difference between file and database? Can files qualify as a database?
Answers : Database provides a systematic and organized way of storing, managing and retrieving from collection of logically related information. Secondly the information has to be persistent, that means even after the application is closed the information should be persisted.
Finally it should provide an independent way of accessing data and should not be dependent on
the application to access the information. Main difference between a simple file and database that database has independent way (SQL) of accessing information while simple files do not File meets the storing, managing and retrieving part of a database but not the independent way of accessing data. Many experienced programmers think that the main difference is that file can not provide multi-user capabilities which a DBMS provides. But if we look at some old COBOL and C programs where file where the only means of storing data, we can see functionalities like locking, multi-user etc provided very efficiently. So it’s a matter of debate if some interviewers think this as a main difference between files and database accept it… going in to debate is probably loosing a job.

2. What is SQL ?
Answers : SQL stands for Structured Query Language.SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. SQL statements are used to retrieve and update data in a database.

3. What’s difference between DBMS and RDBMS ?
Answers : DBMS provides a systematic and organized way of storing, managing and retrieving from collection of logically related information. RDBMS also provides what DBMS provides but above that it provides relationship integrity. So in short we can say
RDBMS = DBMS + REFERENTIAL INTEGRITY
These relations are defined by using “Foreign Keys” in any RDBMS.Many DBMS companies
claimed there DBMS product was a RDBMS compliant, but according to industry rules and
regulations if the DBMS fulfills the twelve CODD rules it’s truly a RDBMS. Almost all DBMS (SQL SERVER, ORACLE etc) fulfills all the twelve CODD rules and are considered as truly RDBMS.

4. What are CODD rules?
Answers : In 1969 Dr. E. F. Codd laid down some 12 rules which a DBMS should adhere in order to get the logo of a true RDBMS.
Rule 1: Information Rule.
"All information in a relational data base is represented explicitly at the logical level and in
exactly one way - by values in tables."
Rule 2: Guaranteed access Rule.
"Each and every datum (atomic value) in a relational data base is guaranteed to be logically
accessible by resorting to a combination of table name, primary key value and column name."
In flat files we have to parse and know exact location of field values. But if a DBMS is truly
RDBMS you can access the value by specifying the table name, field name, for instance
Customers.Fields [‘Customer Name’].
Rule 3: Systematic treatment of null values.
"Null values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported in fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type.".
Rule 4: Dynamic on-line catalog based on the relational model.
"The data base description is represented at the logical level in the same way as ordinary data,
so that authorized users can apply the same relational language to its interrogation as they
apply to the regular data."The Data Dictionary is held within the RDBMS, thus there is no-need for off-line volumes to tell you the structure of the database.
Rule 5: Comprehensive data sub-language Rule.
"A relational system may support several languages and various modes of terminal use (for
example, the fill-in-the-blanks mode). However, there must be at least one language whose
statements are expressible, per some well-defined syntax, as character strings and that is
comprehensive in supporting all the following items
Data Definition
View Definition
Data Manipulation (Interactive and by program).
Integrity Constraints
Authorization.
Transaction boundaries ( Begin , commit and rollback)
Rule 6: .View updating Rule
"All views that are theoretically updatable are also updatable by the system."
Rule 7: High-level insert, update and delete.
"The capability of handling a base relation or a derived relation as a single operand applies not
only to the retrieval of data but also to the insertion, update and deletion of data."
Rule 8: Physical data independence.
"Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representations or access methods."
Rule 9: Logical data independence.
"Application programs and terminal activities remain logically unimpaired when informationpreserving changes of any kind that theoretically permit un-impairment are made to the base tables."
Rule 10: Integrity independence.
"Integrity constraints specific to a particular relational data base must be definable in the
relational data sub-language and storable in the catalog, not in the application programs." Rule
11: Distribution independence.
"A relational DBMS has distribution independence."
Rule 12: Non-subversion Rule.
"If a relational system has a low-level (single-record-at-a-time) language, that low level cannot
be used to subvert or bypass the integrity Rules and constraints expressed in the higher level
relational language (multiple-records-at-a-time)."

5. What are E-R diagrams?
Answers : E-R diagram also termed as Entity-Relationship diagram shows relationship between various tables in the database. .

6. How many types of relationship exist in database designing?
Answers : There are three major relationship models:-
One-to-one
One-to-many
Many-to-many

7. What is normalization? What are different type of normalization?
Answers : There is set of rules that has been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as Normalization.
Benefits of Normalizing your database include:
=>Avoiding repetitive entries
=>Reducing required storage space
=>Preventing the need to restructure existing tables to accommodate new data.
=>Increased speed and flexibility of queries, sorts, and summaries.
Following are the three normal forms :-
First Normal Form
For a table to be in first normal form, data must be broken up into the smallest un possible.In
addition to breaking data up into the smallest meaningful values, tables first normal form should not contain repetitions groups of fields.
Second Normal form
The second normal form states that each field in a multiple field primary keytable must be
directly related to the entire primary key. Or in other words,each non-key field should be a fact
about all the fields in the primary key.
Third normal form
A non-key field should not depend on other Non-key field.

8. What is denormalization ?
Answers : Denormalization is the process of putting one fact in numerous places (its vice-versa of normalization).Only one valid reason exists for denormalizing a relational design - to enhance performance.The sacrifice to performance is that you increase redundancy in database.

9. Can you explain Fourth Normal Form and Fifth Normal Form ?
Answers : In fourth normal form it should not contain two or more independent multi-v about an entity and it should satisfy “Third Normal form”. Fifth normal form deals with reconstructing information from smaller pieces of information. These smaller pieces of information can be maintained with less redundancy.

10. Have you heard about sixth normal form?
Answers : If we want relational system in conjunction with time we use sixth normal form. At this moment SQL Server does not supports it directly.

11. What are DML and DDL statements?
Answers : DML stands for Data Manipulation Statements. They update data values in table. Below are the most important DDL statements:-
=>SELECT - gets data from a database table
=> UPDATE - updates data in a table
=> DELETE - deletes data from a database table
=> INSERT INTO - inserts new data into a database table
DDL stands for Data definition Language. They change structure of the database objects like
table, index etc. Most important DDL statements are as shown below:-
=>CREATE TABLE - creates a new table in the database.
=>ALTER TABLE – changes table structure in database.
=>DROP TABLE - deletes a table from database
=> CREATE INDEX - creates an index
=> DROP INDEX - deletes an index

12. How do we select distinct values from a table?
Answers : DISTINCT keyword is used to return only distinct values. Below is syntax:- Column age and Table pcdsEmp
SELECT DISTINCT age FROM pcdsEmp

13. What is Like operator for and what are wild cards?
Answers : LIKE operator is used to match patterns. A "%" sign is used to define the pattern.
Below SQL statement will return all words with letter "S"
SELECT * FROM pcdsEmployee WHERE EmpName LIKE 'S%'
Below SQL statement will return all words which end with letter "S"
SELECT * FROM pcdsEmployee WHERE EmpName LIKE '%S'
Below SQL statement will return all words having letter "S" in between
SELECT * FROM pcdsEmployee WHERE EmpName LIKE '%S%'
"_" operator (we can read as “Underscore Operator”). “_” operator is the character defined at
that point. In the below sample fired a query Select name from pcdsEmployee where name like
'_s%' So all name where second letter is “s” is returned.

14. Can you explain Insert, Update and Delete query?
Answers : Insert statement is used to insert new rows in to table. Update to update existing data in the table. Delete statement to delete a record from the table. Below code snippet for Insert, Update and Delete :-
INSERT INTO pcdsEmployee SET name='rohit',age='24';
UPDATE pcdsEmployee SET age='25' where name='rohit';
DELETE FROM pcdsEmployee WHERE name = 'sonia';

15. What is order by clause?
Answers : ORDER BY clause helps to sort the data in either ascending order to descending order. Ascending order sort query
SELECT name,age FROM pcdsEmployee ORDER BY age ASC
Descending order sort query
SELECT name FROM pcdsEmployee ORDER BY age DESC

16. What is the SQL " IN " clause?
Answers : SQL IN operator is used to see if the value exists in a group of values. For instance the below SQL checks if the Name is either 'rohit' or 'Anuradha' SELECT * FROM pcdsEmployee WHERE name IN ('Rohit','Anuradha') Also you can specify a not clause with the same. SELECT * FROM pcdsEmployee WHERE age NOT IN (17,16)

17. Can you explain the between clause?
Answers : Below SQL selects employees born between '01/01/1975' AND '01/01/1978' as per mysql SELECT * FROM pcdsEmployee WHERE DOB BETWEEN '1975-01-01' AND '2011-09-28'

18. We have an employee salary table how do we find the second highest from it?
Answers : Below Sql Query find the second highest salary
SELECT * FROM pcdsEmployeeSalary a WHERE (2=(SELECT COUNT(DISTINCT(b.salary)) FROM pcdsEmployeeSalary b WHERE b.salary>=a.salary))

19. What are different types of joins in SQL?
Answers : INNER JOIN
Inner join shows matches only when they exist in both tables. Example in the below SQL there
are two tables Customers and Orders and the inner join in made on Customers.Customerid and
Orders.Customerid. So this SQL will only give you result with customers who have orders. If the customer does not have order it will not display that record.
SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID
=Orders.CustomerID
LEFT OUTER JOIN
Left join will display all records in left table of the SQL statement. In SQL below customers with or without orders will be displayed. Order data for customers without orders appears as NULL values. For example, you want to determine the amount ordered by each customer and you need to see who has not ordered anything as well. You can also see the LEFT OUTER JOIN as a mirror image of the RIGHT OUTER JOIN (Is covered in the next section) if you switch the side of each table.
SELECT Customers.*, Orders.* FROM Customers LEFT OUTER JOIN Orders ON
Customers.CustomerID =Orders.CustomerID
RIGHT OUTER JOIN
Right join will display all records in right table of the SQL statement. In SQL below all orders
with or without matching customer records will be displayed. Customer data for orders without
customers appears as NULL values. For example, you want to determine if there are any orders
in the data with undefined CustomerID values (say, after a conversion or something like it). You can also see the RIGHT OUTER JOIN as a mirror image of the LEFT OUTER JOIN if you switch the side of each table.
SELECT Customers.*, Orders.* FROM Customers RIGHT OUTER JOIN Orders ON
Customers.CustomerID =Orders.CustomerID

20. What is “CROSS JOIN”? or What is Cartesian product?
Answers : “CROSS JOIN” or “CARTESIAN PRODUCT” combines all rows from both tables. Number of rows will be product of the number of rows in each table. In real life scenario I can not imagine where we will want to use a Cartesian product. But there are scenarios where we would like permutation and combination probably Cartesian would be the easiest way to achieve it.

21. How to select the first record in a given set of rows?
Answers : Select top 1 * from sales.salesperson

22. What is the default “-SORT ” order for a SQL?
Answers : ASCENDING


23. What is a self-join?
Answers : If we want to join two instances of the same table we can use self-join.

24. What’s the difference between DELETE and TRUNCATE ?
Answers : Following are difference between them:
=>>DELETE TABLE syntax logs the deletes thus making the delete operations low. TRUNCATE table does not log any information but it logs information about deallocation of data page of the table. So TRUNCATE table is faster as compared to delete table.
=>>DELETE table can have criteria while TRUNCATE can not.
=>> TRUNCATE table can not have triggers.

25. What’s the difference between “UNION” and “UNION ALL” ?
Answers : UNION SQL syntax is used to select information from two tables. But it selects only distinct records from both the table. , while UNION ALL selects all records from both the tables.

26. What are cursors and what are the situations you will use them?
Answers : SQL statements are good for set at a time operation. So it is good at handling set of data. But there are scenarios where we want to update row depending on certain criteria. we will loop through all rows and update data accordingly. There’s where cursors come in to picture.

27. What is " Group by " clause?
Answers : “Group by” clause group similar data so that aggregate values can be derived.

28. What is the difference between “HAVING” and “WHERE” clause?
Answers : “HAVING” clause is used to specify filtering criteria for “GROUP BY”, while “WHERE” clause applies on normal SQL.

29. What is a Sub-Query?
Answers : A query nested inside a SELECT statement is known as a subquery and is an alternative to complex join statements. A subquery combines data from multiple tables and returns results that are inserted into the WHERE condition of the main query. A subquery is always enclosed within parentheses and returns a column. A subquery can also be referred to as an inner query and the main query as an outer query. JOIN gives better performance than a subquery when you have to check for the existence of records.
For example, to retrieve all EmployeeID and CustomerID records from the ORDERS table that
have the EmployeeID greater than the average of the EmployeeID field, you can create a nested query, as shown:
SELECT DISTINCT EmployeeID, CustomerID FROM ORDERS WHERE EmployeeID > (SELECT
AVG(EmployeeID) FROM ORDERS)

30. What are Aggregate and Scalar Functions?
Answers : Aggregate and Scalar functions are in built function for counting and calculations.
Aggregate functions operate against a group of values but returns only one value. AVG(column) :- Returns the average value of a column
COUNT(column) :- Returns the number of rows (without a NULL value) of a column
COUNT(*) :- Returns the number of selected rows
MAX(column) :- Returns the highest value of a column
MIN(column) :- Returns the lowest value of a column
Scalar functions operate against a single value and return value on basis of the single value.
UCASE(c) :- Converts a field to upper case
LCASE(c) :- Converts a field to lower case
MID(c,start[,end]) :- Extract characters from a text field
LEN(c) :- Returns the length of a text

31. Can you explain the SELECT INTO Statement?
Answers : SELECT INTO statement is used mostly to create backups. The below SQL backsup the Employee table in to the EmployeeBackUp table. One point to be noted is that the structure of pcdsEmployeeBackup and pcdsEmployee table should be same.
SELECT * INTO pcdsEmployeeBackup FROM pcdsEmployee

32. What is a View?
Answers : View is a virtual table which is created on the basis of the result set returned by the select statement.
CREATE VIEW [MyView] AS SELECT * from pcdsEmployee where LastName = 'singh'
In order to query the view
SELECT * FROM [MyView]


No comments:

Post a Comment

Subscribe to: Post Comments (Atom)