SQL

Syntax : CREATE DATABASE Data_Base_Name;

Example : CREATE DATABASE JSTDB;

Syntax : DROP DATABASE Data_Base_Name;

Example : DROP DATABASE JSTDB;

Syntax : CREATE TABLE TABLE_NAME (COLUMN_1 DATA_TYPE, COLUMN_2 DATA_TYPE, ... COLUMN_N DATA_TYPE);

Example : CREATE TABLE STUDENT (ID INT, FULL_NAME VARCHAR(50), ADDRESS VARCHAR(150), CITY VARCHAR(50), STATE VARCHAR(50));

Syntax : CREATE TABLE NEW_TABLE_NAME AS SELECT COLUMN_1, COLUMN_2, ...... FROM EXISTING_TABLE_NAME;

Example : CREATE TABLE STUDENT_NEW AS SELECT ID, FULL_NAME, ADDRESS, CITY, STATE FROM STUDENT_OLD;

Syntax : DROP TABLE TABLE_NAME;

Example: DROP TABLE STUDENT_OLD;

Syntax : TRUNCATE TABLE TABLE_NAME;

Example: TRUNCATE TABLE STUDENT_OLD;

Syntax : ALTER TABLE TABLE_NAME ADD COLUMN_NAME DATA_TYPE;

Example : ALTER TABLE EMPLOYEE ADD STATE VARCHAR(100);

Syntax : ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME;

Example : ALTER TABLE EMPLOYEE DROP COLUMN STATE;

Syntax : ALTER TABLE TABLE_NAME MODIFY COLUMN COLUMN_NAME DATA_TYPE;

Example : ALTER TABLE EMPLOYEE MODIFY COLUMN STATE VARCHAR(50);

Syntax : SELECT COLUMN_1, COLUMN_2, ....... FROM TABLE_NAME;

Example : SELECT * FROM PERSON;

Example : SELECT ID, NAME FROM PERSON;

Syntax : SELECT DISTINCT COLUMN_1, COLUMN_2, ... FROM TABLE_NAME;

Example : SELECT DISTINCT STATE FROM EMPLOYEE;

SELECT COUNT(DISTINCT STATE) FROM EMPLOYEE;

Syntax : SELECT COLUMN_1, COLUMN_2, ... FROM TABLE_NAME WHERE CONDITION;

Example : SELECT * FROM EMPLOYEE WHERE STATE='BIHAR';

Example : SELECT * FROM EMPLOYEE WHERE ID = 100;

Definition : Returns records that have matching values in both tables.

Example : SELECT * FROM EMPLOYEE INNER JOIN DEPARTMENT USING (DEPTNO);

Definition : Returns all records from the left table and the matched records from the right table.

Example : SELECT * FROM EMPLOYEE E, DEPARTMENT D WHERE EMPLOYEE.DEPTNO = DEPARTMENT.DEPTNO(+);

Example : SELECT * FROM EMPLOYEE E LEFT OUTER JOIN DEPARTMENT D ON (E.DEPTNO = D.DEPTNO);

Definition : Returns all records from the right table and the matched records from the left table.

Example : SELECT * FROM EMPLOYEE E, DEPARTMENT D WHERE EMPLOYEE.DEPTNO(+) = DEPARTMENT.DEPTNO;

Example : SELECT * FROM EMPLOYEE E RIGHT OUTER JOIN DEPARTMENT D ON (E.DEPTNO = D.DEPTNO);

Definition : Returns all records when there is a match in either left or right table.

Example : SELECT * FROM EMPLOYEE E FULL OUTER JOIN DEPARTMENT D ON (E.DEPTNO = D.DEPTNO);

SELECT TOP 1 SALARY FROM (SELECT DISTINCT TOP N SALARY FROM EMPLOYEE ORDER BY SALARY ASC) ORDER BY SALARY WHERE N > 1;

SELECT TOP 1 SALARY FROM (SELECT DISTINCT TOP N SALARY FROM EMPLOYEE ORDER BY SALARY DESC) ORDER BY SALARY WHERE N > 1;

select * from employee where rowid not in (select max(rowid) from employee group by empno);

select * from department where deptno = (select distinct (deptno) from employee where job = 'Sales Man');

select * from employee where mgr in (select mgr from employee group by mgr having count(*) ≥ 2) order by mgr;

select * from department where deptno in (select deptno from employee group by deptno having count(*) ≥ 5);

select * from employee where mgr = (select empno from employee where ename = 'King');

select * from employee e where sal = (select max(sal) from employee group by deptno);

select * from employee e where sal = (select max(sal) from employee where deptno = 10);

select * from employee e where sal = (select count(distinct (sal)) from employee where sal ≥ e.sal);

SELECT * FROM EMPLOYEE WHERE SAL = (SELECT MAX(SAL) FROM EMPLOYEE WHERE SAL < (SELECT MAX(SAL) FROM EMPLOYEE);

SELECT EMPNO, ENAME FROM EMPLOYEE WHERE DEPTNO = (SELECT DEPTNO FROM DEPARTMENT WHERE DNAME = 'RESEARCH');

SELECT * FROM EMPLOYEE WHERE SAL = (SELECT MAX(SAL) FROM EMPLOYEE);