Some Important Topics
Define Relation, attribute, tuple, field, candidate key, Alternate key, Primary Key, Foreign Key.
DATA and Data Types
Differentiate between char and varchar2
Concept of string, Number and Date values.
DATA INTEGRITY (Domain and Referential Integrity)
Normalisation ( First, second, third, BCNF)
EXAMPLES of commercially available Database Management System’s (BACK END)
1) Oracle
2) MS-SQL server
3) DB2
4) MYSQL
5) SYBASE
6) INGRES
EXAMPLES of Front End Software’s
1) Oracle Developer
2) Visual Basic
3) Visual C++
4) Power Builder
5) Delphi
RDBMS Tool :ORACLE (Introduction, Version)
Two Tier and Three Tier
CLASSIFICATION OF SQL STATEMENTS
1) DML (SELECT, INSERT, UPDATE, DELETE)
2) DDL (CREATE , DROP, ALTER, RENAME)
3) TCL (COMMIT, ROLLBACK)
Imp :What is significance of Null value and null values in arithmetic operation.
REVISING SELECT STATEMENT
SELECTING ALL COLUMNS
Select * from Emp;
SELECTING SPECIFIC COLUMN
Select ecode, ename, sal from emp;
COLUMN HEADING & ARITHMETIC EXPRESSION
SELECT empno,ename,sal, sal+sal*.15 “New Salary” from emp;
New Salary will be given as heading for the expression sal+sal*.15
PUTTING TEXT IN QUERY OUTPUT.
Select salesman_name, comm.*100,’%’ from salesman;
OUTPUT
SALESMAN_NAME COMM*100%
Ajay 13.00%
Amit 11.00%
CONCATENATION OPERATOR ||
SELECT ename || job “employee”, sal*12 “total salary” from emp;
Employee total salary
SMITHCLERK 9600
DULICATE ROWS AND THEIR ELIMINATION (distinct )
SELECT DISTINCT(SAL) FROM EMP;
IT WILL ELIMINATE THE ROWS HAVING DUPLICATE VALUE FOR SAL COLUMN
SELECTING SPECIFIC ROWS (WHERE CLAUSE)
SELECT empname, sal from emp where sal >2900;
RELATIONAL OPERATORS
=, >, <, <=,>=,<>
LOGICAL OPERATOR (OR, AND ,NOT)
SELECT * FROM EMP WHERE (GRADE=’E2’ OR GRADE=’E3’);
SELECT * FROM EMP WHERE (GRADE=’E2’ AND GROSS<9000); SELECT * FROM EMP WHERE (NOT GRADE=’G1’); BETWEEN OPERATOR
SELECT ENAME,SAL FROM EMP WHERE SAL BETWEEN 5000 AND 10000;
SELECT ENAME,SAL FROM EMP WHERE SAL NOT BETWEEN 1000 AND 5000;
IN OPERATOR
SELECT * FROM EMP WHERE SAL IN( 5000, 7000,8000);
SELECT * FROM EMP WHER JOB IN (‘MANGER’,’SALESMAN’,’ANALYST’);
LIKE OPERATOR( % FOR SUBSTRING, _ FOR A CHARACTER)
SELECT ename from emp where ename LIKE ‘M_L%’;
SEARCHING FOR NULL
SELECT ename, comm. From emp where comm. IS NULL;
SORTING ( ORDER BY)
SELECT * FROM EMP ORDER BY ENAME;
SELECT * FROM EMP WHERE SAL>2000 ORDER BY ENAME;,br>
SELECT * FROM EMP WHERE SAL >2000 ORDER BY ENAME DESC;
DISPLAYING TABLE STRUCTURE
DESC EMP;
SQL FUNCTIONS
Functions is predefined set of commands which performs some operation and return a single value.
SQL functions are classified as
1) Single row
2) Group functions (Multiple row)
Single Row Functions: works with single row at a time i.e it returns a result for every row of a queried table.
1) Character functions
A) LOWER()
B) INITCAP()
C) UPPER()
D) CONCAT()
E) INSTR() *****
F) LENGTH()
H) TRIM() *******
I)SUBSTR() *******IMP.
J)LPAD() ******
2) Numeric functions
A) ROUND() ********IMP
B)TRUNC()
C)MOD() ********IMP
3) Date Functions
A) LAST_DAY()
B)MONTHS_BETWEEN()
C)NEXT_DAY()
D)ADD_MONTHS()
4) Miscellaneous Functions
A) NVL
B) DECODE
5)Data type conversion functions.
A) TO_CHAR *******IMP
B) TO_NUMBER ******IMP
Multiple Row or Group Functions: works with data of multiple rows at a time and return a aggregated value.
1) AVG
SELECT AVG(SAL) “Average” from emp;
2) Min
SELECT MIN(SAL) “MINIMUM” FROM EMP;
3) Max
SELECT MAX(SAL) “MAXIMUM” FROM EMP;
4) Count (*, DISTINCT, ALL)
TO COUNT NUMBER OF RECORDS IN EMP TABLE
SELECT COUNT(*) “TOTAL” FROM EMP;
TO COUNT NUMBER OF DISTINCT JOBS IN TABLE EMP
SELECT COUNT(DISTINCT JOB) “JOBS” FROM EMP;
TO COUNT ENTRIES INCLUDING REPEATS
SELECT COUNT( ALL JOB) “JOBS” FROM EMP;
5)Sum
SELECT SUM(SAL) “TOTAL” FROM EMP;
********************************************************
GROUP BY CLAUSE
The GROUP BY clause combines all those records that have identical values in a particular field or group of fields.
Grouping can be done by a column name or with group functions.
SELECT job, count(*), Sum(comm) FROM emp GROUP BY job;
A Group within another group is called nested group. Nested grouping can be done by providing multiple fields in the GROUP BY expression.
SELECT deptno,job,count(empno) from emp group by deptno,job;
***In the select list of group, only those fields or expressions can be included that either return single value for a group or are constants.
Upto 10 fields or 10 levels of grouping can be specified.
PLACING CONDITIONS ON GROUPS---HAVING CLAUSE
The HAVING Clause places conditions on GROUPS in contrast to WHERE clause that places conditions on individual rows.
SELECT AVG(GROSS), SUM(GROSS) from employee GROUP BY grade HAVING grade=’E4’;
JOINS
A join is a query that combines rows from two or more tables.
1) In a join query more than one table are listed in FROM clause.
2) In WHERE clause, two or more tables having common columns are compared.
EXAMPLE:
SELECT emp.deptno,dname,empno,ename,job,sal FROM emp,dept WHERE
Emp.deptno=dept.deptno;
TABLE ALIAS
A Table Alias is a temporary label given along with table name in FROM clause.
EXAMPLE:
SELECT e.deptno,dname,empno,ename,job,sal
FROM emp E, dept D
WHERE e.deptno=d.deptno
ADDITIONAL SEARCH CONDITIONS IN JOINS USING AND OPERATOR
EXAMPLE:
SELECT e.deptno,dname,empno,ename,job,sal
FROM emp E, dept D
WHERE e.deptno=d.deptno
AND dname=’SALES’
ORDER BY e.deptno,empno;
Types of JOINS
INNER JOIN
An Inner join selects only those rows from both( or all ) the joining tables, that have satisfied the join condition.
1) EQUI JOIN
2) NON- EQUI JOIN
3) SELF JOIN
OUTER JOIN
An Outer join selects all the rows that satisfies the join condition and those rows from one table for which no rows from the other table satisfies the join condition.
1) LEFT OUTER JOIN
2) RIGHT OUTER JOIN
INNER JOINS
EQUI JOIN:
The join in which columns are compared for equality., is called equi join.
EXAMPLE:
SELECT * FROM shipments,items WHERE shipments.item=items.item;
NON- EQUI JOIN :
A NON-EQUI join is a query that specifies some relationship other than equality (= sign )between the columns.
IMPLEMENTATION OF NON-EQUI JOIN.
NON-EQUI join is used if we have to join the tables having no common column.
EXAMPLE:
SELECT ENAME,JOB,SAL,GRADE
FROM EMP E,SAL_GRADE S
WHERE SAL BETWEEN LOSAL AND HISAL
AND JOB=’ANALYST’
SELF JOIN:
A TABLE JOINED WITH ITSELF IS CALLED SELF JOIN.
SELF JOIN is used when values within a column of a table are to be compared.
EXAMPLE
SELECT t1.patient_no, t2.patient_no, phy_id
FROM treats t1, treats t2
WHERE t1.phy_id=t2.phy_id
AND t1.patient_no<>t2.patient_no;
OUTER JOINS
USAGE OF OUTER JOIN:
If we want to view all the rows that satisfy the join condition and those rows from one table for which no rows from the other table satisfy the join condition.
(+) operator is used for OUTER JOIN, which simply means “show me everything even if values in rows are not matching or something is missing.
EXAMPLE:
SELECT ename,job,dept.deptno,dname
FROM emp,dept
WHERE emp.deptno(+)=dept.deptno;
1) LEFT OUTER JOIN:
The outer join that returns all the rows of first or left table, is called left outer join.
(above given is example of left outer join)
2) RIGHT OUTER JOIN
The outer that returns all the rows of second or right table, is called right outer join.
(+ operator is used with column on right side of ‘=’ sign)
SUBQUERIES
A subquery (also called Nested Query) refers to a query statement that appears inside another SQL statement.
1) The statement containing a subquery is called the parent statement. Parent statement or the main query is processed before the subquery.
2) SQL allows us to specify subqueries in WHERE clause of other queries.
3) Subqueries can themselves have other subqueries.
EXAMPLE:(OF SINGLE ROW OR SINGLE VALUED SUBQUERY)
SELECT ename,deptno FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename=’SCOTT’);
Ques: Find out who earns the least salary.
Ans:
SELECT ename FROM emp WHERE SAL=(SELECT min(sal) FROM emp);
TYPES OF SUBQUERIES:
1) Single Row- RETURNS single row in its result.
2) Multiple Row- RETURNS multiple rows in its result.
3) Single Column – RETURNS one column in its result.
4) Multiple Column – RETURNS multiple columns in its result.
****>
OPERATORS USED IN SUBQUERIES ( ANY,ALL,EXIST,IN)
MULTI VALUED OR MULTIPLE ROW
SELECT item_code, description FROM item WHERE item_code IN
(SELECT item_code FROM billed WHERE patient_no=1116);
SELECT * FROM emp WHERE sal>=ALL (SELECT avg(sal) FROM emp GROUP BY job);
SELECT * FROM emp WHERE sal >ANY(SELECT sal FROM emp WHERE deptno=30) ORDER BY sal desc;
MULTI-COLUMN
SELECT item_code, description FROM item WHERE EXISTS
(SELECT * FROM billed WHERE item.item_code=billed.item_code);
IMP What do you understand by schema objects?
A Schema refers to the collection of logical structures of data.
Some schema objects are:
Tables, Views, Procedures, Functions, packages, triggers.
VIEWS
A virtual table that draws its data from single or joined table(s) as the result of an SQL Select statement.
A view is a virtual table that does not exist in reality, but is a logical definition of set of related columns from single or joined table(s).
AFTER CREATING A VIEW FROM TWO OR MORE TABLES THAT VIEW
CAN BE ACCESSED AS A SINGLE TABLE USING SIMPLE SELECT COMMAND.
A view definition is permanently stored as part of the database.
TYPES OF VIEWS
VIEWS ARE CLASSIFIED AS
SIMPLE VIEW COMPLEX VIEW
Views generated from data in one table are termed as simple views. Views generated from data of more than one table or use of subquery.
We can modify a simple view We cannot modify a complex view.
MODIFYING A VIEW
We can (INSERT, UPDATE OR DELETE) modify a view generated from single table (simple view).
EXAMPLE:
CREATE VIEW emp_sal AS
SELECT * FROM emp WHERE sal >3000;
NOTE VIEWS WHICH WE CAN’T MODIFY
We cannot modify(INSERT, UPDATE OR DELETE) a complex view.(a view generated from more than one table or having a subquery)
CREATE VIEW patient_bill AS
SELECT b.patient_no,p.pat_name,b.itemcode,charge,description,date_discharged
FROM patient p, billed b, item i
WHERE p.patient_no=B.patient_no AND i.item_code=b.item_code;
ALSO
1) We cannot modify the simple view , If we include READ ONLY clause in the end of the view definition.
CREATE VIEW emp_sal AS
SELECT * FROM emp WHERE sal >3000
WITH READ ONLY;
2) We cannot insert data into views if the underlying table has NOT NULL constraints on it and those columns are not included in a view definition.
3) We cannot modify simple view if the select statement creating a view has GROUP BY clause.
SYNONYMS
A synonym is an alternative name for a table, view, sequence, procedure, stored function, package or another synonym.
Significance of synonym
They simplify the SQL statements( by using shorter or easier names) and hide the complexity beneath
.
TYPES OF SYNONYMS
1) PUBLIC ( Created by a user and that can be used by all other users.
2) PRIVATE( Created by the user for personal use)
SYNTAX
CREATE SYNONYM stu FOR Student;
CREATE PUBLIC SYNONYM stu1 FOR Student;
SEQUENCES
A Sequence is a special database object that generates integers according to specified rules at the time the sequence was created.
EXAMPLES:
CREATE SEQUENCE counting
START WITH 1
INCREMENT BY 3
MAXVALUE 150
ORDER;
CREATE SEQUENCE counting2
MINVALUE 50
MAXVALUE 150
ORDER;
SYNTAX:
CREATE SEQUENCE sequence_name
START WITH integervalue
INCREMENT BY integervalue
MAXVALUE inegervalue/NOMAXVALUE
MINVALUE integervalue/NOMINVALUE
CYCLE/NOCYCLE
ORDER/NOORDER;
INDEX
An INDEX is a sorted list of data from one or more columns in the table that are commonly used as selection criteria.
CREATING TABLES
EXAMPLE:
CREATE TABLE employee( ecode integer, ename char(20),sal number);
DATA INTEGRITY THROUGH CONSTRAINTS.
A constraint refers to a condition or a check that is applied to a column(field ) or set of columns.
The Constraints applied to maintain data integrity are also known as integrity constraints.
Database integrity constraints:-
1) Unique Constraint: Ensures that no two rows have same value in the specified column(s).
2) Primary Key Constraint: Declares a column as the Primary Key of the table.
*** The Primary Key not allow NULL values , so this constraint most be applied to columns declared NOT NULL.
***Similar to UNIQUE CONSTRAINT except that only one column(or group of columns) can be applied this constraint
.
3) Default Constraint: A default value can be specifies for columns using this clause.
4) Check Constraint: Limits values that can be inserted in a column.
EXAMPLE:
CREATE TABLE employee
( ecode integer NOT NULL PRIMARY KEY,
ename char(20) NOT NULL,
grade char(2) DEFAULT=’E1’,
gross decimal CHECK (gross>2000));
5) Foreign Key Constraint: This is used to relate two tables by a common column( or set of columns)
CREATE TABLE items
( itemno char(5) NOT NULL PRIMARY KEY,
);
CREATE TABLE orders
( orderno number(6) NOT NULL PRIMARY KEY,
itemno char(5) REFRENCES items(itemno),
);
Assigning Names to constraints:
By default, Oracle assigns a unique name to each constraint defined by the user as
SYS_Cn
Where n is the integer which makes constraint name unique Eg: SYS_C001211
You can so name a constraint created by you.
CREATE TABLE items
(itemno char(5) CONSTRAINT p_key NOT NULL PRIMARY KEY,
);
p-key is the name assigned to Primary Key Constraint.
REMOVING INTEGRITY CONSTRAINTS
BY ALTER TABLE- DROP CLAUSE
ALTER TABLE dept
DROP PRIMARY KEY CASCADE ;
The CASCADE option drops any foreign keys that reference the primary key.
ALTER TABLE dept
DROP UNIQUE(dname)
ENABLING OR DISABLING CONSTRAINTS
BY ALTER TABLE-ENABLE/DISABLE
ALTER TABLE products
Enable pk_products;
Where pk_products is the name given to constraint during create table command.
OTHER SQL COMMANDS
1) Desc
Is used to view the structure of the table.
2) INSERT INTO
INSERT INTO emp VALUES (201,’MANJU’,4670);
Inserting through parameter substitution
INSERT INTO result
VALUES( &rollno,’&name’,&marks);
Inserting Data from Another Table
INSERT INTO branch1
SELECT * FROM branch2
WHERE gross>7000.00;
3)Deleting Data
DELETE FROM
WHERE
DELETE FROM emp
WHERE gross <2000; 4)MODIFYING DATA (UPDATE command)
UPDATE items
SET rol=250; qoh=700
WHERE icode<600; UPDATE emp SET sal=NULL WHERE ecode=1135; 5) ALTERING TABLE
To add column:
ALTER TABLE emp
ADD(tel_number integer);
To drop column
ALTER TABLE emp
DROP COLUMN sal;
To Modifying existing column (width,datatype)
ALTER TABLE emp
MODIFY(jod char(30));
6) TRUNCATING TABLES
USED TO DELETE ALL THE ROWS FROM TABLE QUICKLY WHILE LEAVING THE DEFINITION OF THE TABLE INTACT.
TRUNCATE TABLE emp;
6) COMMIT, ROLLBACK, SAVEPOINT
7) DROP table
8) DROP VIEW
9) DROP SYNONYM
10) GRANT command is used to grant privileges.
GRANT AND REVOKE PRIVILEGES
GRANT ALL
ON producks
To tupur;
= above command is used to give all the privileges to user TUPUR on table PRODUCTS.
11) REVOKE command is used to revoke privileges.
REVOKE SELECT
ON orders
FROM ritu;
Will revoke SELECT privilege on ORDERS table from user RITU.
