Lab Manual
Name
Father name
Roll No
Semester 1 st
Class MSC(IT)
Subject Database system
Submitted To Mam Nosheen
Ashraf
Submitted by
Government College
Women University Faisalabad
Lab 1
DML (DATA MANIPULATION LANGUAGE ) QUERIES.
1- SQL> Select * from cat;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
2- SQL >Select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.
3- SQL> Describe Emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
4- SQL> Specific column;
SP2-0734: unknown command beginning "Specific c..." - rest of line ignored.
SQL> Select empno, ename,job from emp;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
EMPNO ENAME JOB
---------- ---------- ---------
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
14 rows selected.
5- SQL> Select job from emp;
JOB
---------
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
JOB
---------
CLERK
ANALYST
CLERK
14 rows selected.
6- SQL> Select Distinct job from emp;
JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
7- SQL> Select empno,ename job from emp where empno=7902;
EMPNO JOB
---------- ----------
7902 FORD
SQL> Select empno,ename job from emp where ename='FORD';
EMPNO JOB
---------- ----------
7902 FORD
8- SQL> Select empno,ename job from emp where ename= 'ford';
no rows selected
Operators in SQL
Different types of operators in SQL are as follows:
1- Comparison Operator
A comparison (or relational) operator is a mathematical symbol which is used to compare two
values. Comparison operators are used in conditions that compares one expression with
another. The result of a comparison can be true or false.
SQL> select empno, sal, comm from emp where sal<=comm;
EMPNO SAL COMM
--------- ---------- ----------
7654 1250 1400
2- Between Operator
The between operator selects values within a given range. The values can be numbers, text, or
dates. Between operator is inclusive: begin and end values are included.
SQL> select empno, ename, sal from emp where sal between 1000 and 1500;
EMPNO ENAME SAL
---------- ---------- ----------
7521 WARD 1250
7654 MARTIN 1250
7844 TURNER 1500
7876 ADAMS 1100
7934 MILLER 1300
3- IN Operator
The IN operator is used to test for values in a specified list. It can be used with any data type.
SQL> select empno, job from emp where job in ('CLERK', 'MANAGER');
EMPNO JOB
---------- ---------
7369 CLERK
7566 MANAGER
7698 MANAGER
7782 MANAGER
7876 CLERK
7900 CLERK
7934 CLERK
7 rows selected.
Logical Operator
There are three logical operators are as follows:
1- NOT Operator
The SQL NOT condition (sometimes called the NOT Operator) is used to negate a condition in
the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement.
Query:
SQL> select empno, job from emp where job NOT in ('CLERK', 'MANAGER');
EMPNO JOB
---------- ---------
7499 SALESMAN
7521 SALESMAN
7654 SALESMAN
7788 ANALYST
7839 PRESIDENT
7844 SALESMAN
7902 ANALYST
7 rows selected.
2- AND Operator
The AND operator displays a row if ALL condition listed are true.
Query
SQL> select EMPNO, ename, job, sal from emp where sal<=1500 AND job = 'CLERK';
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7934 MILLER CLERK 1300
3- OR Operator
The OR operator displays a row if ANY of the conditions listed are true.
Query
SQL> select EMPNO, ename, job, sal from emp where sal<=1500 OR job = 'MANAGER';
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7934 MILLER CLERK 1300
10 rows selected.
LIKE Operator
The LIKE operator is used to specify a search for a pattern in a column. The character pattern
matching operation may be referred to as “Wild Card” search. Two symbols can be used to
construct the search string.
. The % symbol represents any sequence of zero or more character.
. The _ symbol represents any single character.
Query
SQL> select empno, ename, job from emp where ename Like 'A%';
EMPNO ENAME JOB
---------- ---------- ---------
7499 ALLEN SALESMAN
7876 ADAMS CLERK
SQL> select empno, ename, job from emp where ename like '%A%';
EMPNO ENAME JOB
---------- ---------- ---------
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7876 ADAMS CLERK
7900 JAMES CLERK
Operator Precedence
Operator precedence describes the order in which operations are performed when an
expression is evaluated. Operations with a higher precedence are performed before those with
a lower precedence. Parentheses has the highest precedence and OR has the lowest.
SQL> select empno, ename, 12*sal + 100 from emp;
EMPNO ENAME 12*SAL+100
---------- ---------- ----------
7369 SMITH 9700
7499 ALLEN 19300
7521 WARD 15100
7566 JONES 35800
7654 MARTIN 15100
7698 BLAKE 34300
7782 CLARK 29500
7788 SCOTT 36100
7839 KING 60100
7844 TURNER 18100
7876 ADAMS 13300
EMPNO ENAME 12*SAL+100
---------- ---------- ----------
7900 JAMES 11500
7902 FORD 36100
7934 MILLER 15700
14 rows selected.
Draw dependency diagram showing all functional dependenciesThe SQL ORDER BY clause is
used to sort the data in ascending or descending order, based on one or more columns. Some
databases sort the query results in an ascending order by default.
1- Descending Order
SQL> select empno, ename, deptno from emp order by deptno, sal DESC;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7839 KING 10
7782 CLARK 10
7934 MILLER 10
7788 SCOTT 20
7902 FORD 20
7566 JONES 20
7876 ADAMS 20
7369 SMITH 20
7698 BLAKE 30
7499 ALLEN 30
7844 TURNER 30
EMPNO ENAME DEPTNO
---------- ---------- ----------
7654 MARTIN 30
7521 WARD 30
7900 JAMES 30
14 rows selected.
2- Ascending Order
SQL> select empno, ename, deptno from emp order by deptno, sal;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7934 MILLER 10
7782 CLARK 10
7839 KING 10
7369 SMITH 20
7876 ADAMS 20
7566 JONES 20
7788 SCOTT 20
7902 FORD 20
7900 JAMES 30
7654 MARTIN 30
7521 WARD 30
EMPNO ENAME DEPTNO
---------- ---------- ----------
7844 TURNER 30
7499 ALLEN 30
7698 BLAKE 30
14 rows selected.
Case Conversion Function
1- LOWER Case Function
The LOWER function converts all letters in the specified string to lowercase. If there are
characters in the string that are not letters, they are unaffected by this function.
SQL> select empno, sal, job, LOWER(ename) from emp where ename = 'MILLER';
EMPNO SAL JOB LOWER(ENAM
---------- ---------- --------- ----------
7934 1300 CLERK miller
2- Upper Case Function
The SQL UPPER function converts all the letters in a string into uppercase. If you want to
convert a string to lowercase, you use the LOWER function instead.
3- INITCAP Case Function
INITCAP function sets the first letter of each word in uppercase, all other letters in lowercase.
Words are delimited by white space or characters that are not alphanumeric. A string whose
first character in each word will be converted to uppercase and the rest characters will be
converted to lowercase.
SQL> select empno, job, sal, INITCAP(ename) from emp where LOWER(ename) ='blake';
EMPNO JOB SAL INITCAP(EN
---------- --------- ---------- ----------
7698 MANAGER 2850 Blake
Character Manipulation Function
CONCAT
SUBSTR
Length
InSTR
RPA and LAP
