UNIT-2

JOINS

A join is a query that combines rows from two or more tables. In a join- query, more than

one table are listed in FROM clause. 

This query will give you the Cartesian product i.e. all possible concatenations are formed of all rowsof both the tables EMPL and DEPT. Such an operation is also known as Unrestricted Join. It returns

n1 x n2 rows where n1 is number of rows in first table and n2 is number of rows in second table. 

It returns  c1+c2 columns where c1 is numbers of columns in first table and c2 is number of columns in second table

EQUI-JOIN

- The join in which columns are compared for equality, is called Equi - Join. In equi-join, all the

columns from joining table appear in the output even if they are identical.

e.g. SELECT * FROM empl, dept

WHERE empl.deptno = dept.deptno ; 

Q: with reference to empl and dept table, find the location of employee SMITH.

ename column is present in empl and loc column is present in dept. In order to obtain the result, we

have to join two tables.

SELECT ENAME, LOC

FROM EMPL, DEPT

WHERE EMPL.DEPTNO = DEPT.DEPTNO AND ENAME=’SMITH’; 

TABLE ALIAS

- A table alias is a temporary label given along with table name in FROM clause.

e.g.

SELECT E.DEPTNO, DNAME,EMPNO,ENAME,JOB,SAL

FROM EMPL E, DEPT D

WHERE E.DEPTNO = DEPT.DEPTNO

ORDER BY E.DEPTNO;

In above command table alias for EMPL table is E and for DEPT table , alias is D.

Q: Display details like department number, department name, employee number, employee

name, job and salary. And order the rows by employee number with department number. These

details should be only for employees earning atleast Rs. 6000 and of SALES department.

SELECT E.DEPTNO, DNAME,EMPNO, ENAME, JOB, SAL

FROM EMPL E, DEPT D

WHERE E.DEPTNO = D.DEPTNO

AND DNAME=’SALES’

AND SAL>=6000

ORDER BY E.DEPTNO; 

NATURAL JOIN

By default, the results of an equijoin contain two identical columns. One of the two identical

columns can be eliminated by restating the query. This result is called a Natural join.

e.g. SELECT empl.*, dname, loc

FROM empl,dept

WHERE empl.deptno = dept.deptno ; 

empl.* means select all columns from empl table. This thing can be used with any table.

The join in which only one of the identical columns(coming from joined tables) exists, is called

Natural Join. 

SQL Queries

Creating table: 

CREATE TABLE employee 

(ECODE int , ENAME varchar(20) , GENDER char(1) , GRADE char(2) , GROSS int ) ;

Inserting record: 

INSERT INTO employee VALUES(1001 , ‘Ravi’ , ‘M’ , ‘E4’ , 50000); 

To select only ENAME, GRADE and GROSS column from student table 

SELECT ENAME , GRADE , GROSS FROM EMPLOYEE ;

to retrieve everything (all columns) from a table student/Select all records from tabl :

SELECT * FROM student ;

To display details of employee whose name ends with ‘y’. 

SELECT * FROM EMPLOYEE WHERE ENAME LIKE ‘%y’ ;

To display names of employee whose name starts with R in EMPLOYEE table, the command is : 

SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE ‘R%’ ;

To display details of employee whose second character in name is ‘e’

 SELECT * FROM EMPLOYEE WHERE ENAME LIKE ‘_e%’ ; 

To display details of employee whose  grade value is A1 or A2 : 

SELECT * FROM EMPLOYEE WHERE GRADE IN (‘A1’ , ‘A2’); 

To display ECODE, ENAME and GRADE of those employees whose salary is between 40000 and 50000, command is:

SELECT ECODE , ENAME ,GRADE FROM EMPLOYEE WHERE GROSS BETWEEN 40000 AND 50000 ;

To display the details of employees in EMPLOYEE table in alphabetical order, we use command :

SELECT * FROM EMPLOYEE ORDER BY ENAME ;

Display list of employee in descending alphabetical order whose salary is greater than 40000.

SELECT ENAME FROM EMPLOYEE 

WHERE GROSS > 40000 ORDER BY ENAME desc ;

Change the salary of employee in EMPLOYEE table having employee code 1009 to 55000. 

UPDATE EMPLOYEE SET GROSS = 55000 WHERE ECODE = 1009 ;

 Update the salary to 58000 and grade to B2 for those employee have employee code is 1001.

UPDATE EMPLOYEE SET GROSS = 58000, GRADE=’B2’ WHERE ECODE = 1009 ; 

Double the salary of employees having grade as ‘A1’ or ‘A2’ . 

UPDATE EMPLOYEE SET GROSS = GROSS * 2 WHERE GRADE=’A1’ OR GRADE=’A2’ ;

To remove the details of those employee from EMPLOYEE table whose grade is A1.

DELETE FROM EMPLOYEE WHERE GRADE =’A1’ ;

To delete all records from employee table : 

DELETE FROM EMPLOYEE ;

To add a new column ADDRESS to the EMPLOYEE table, we can write command as :

 ALTER TABLE EMPLOYEE ADD ADDRESS VARCHAR(50);

In table EMPLOYEE , change the datatype of GRADE column from CHAR(2) to VARCHAR(2). 

ALTER TABLE EMPLOYEE MODIFY GRADE VARCHAR(2);

In table EMPLOYEE , change the column ENAME to EM_NAME and data type from VARCHAR(20) to VARCHAR(30).

ALTER TABLE EMPLOYEE CHANGE ENAME EM_NAME VARCHAR(30);

To delete column GRADE from table EMPLOYEE, we will write :

ALTER TABLE EMPLOYEE DROP GRADE ;

Group By : clause used to create group of records from a table