SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database

It is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system

SQL Server is a Microsoft product used to manage and store information. Technically, SQL Server is a “relational database management system” (RDMS).

Top 5 Databases for Web Developers

MySQL Community Server
Microsoft SQL Server
Microsoft Access
Oracle Expression Edition
DB2 Express-C

Advantage or Benefit of RDBMS

1. Consistency
2. Recoverability
3. Distributability
4. Support for IV generation languages
5. Transaction

ASSIGNMENTS WITH SESSION & TOPIC

SESSION - 1

· Create a database company.

· Create a table Employee with following column: -

COLUMN NAME	DATA TYPE
ID	INT
NAME	VARCHAR (50)
ADDRESS	VARCHAR (50)
SALARY	MONEY
DEPARTMENT	VARCHAR (50)

· Add 10 Record

· Display Details where employee id is 4

· Display Detail where employee id is 5 and 6

· Display Detail where employee name is Seema or Specific.

· Remove Detail where employee id is 3

· Remove Detail where employee name is Priya or specific given in a table.

SESSION -2

· Remove Detail of Employee

· Update Record where employee name is Piya with Piya Tyagi.

· Add a column Name Mobile No

· Drop a Column Name Mobile No

· Add a column gender to accept – M, F, O only

· Insert record with Female, Male complete mean.

· Modify column Size of Gender.

· Increment in Salary of 500

SESSION -3

· Fetch Record Where Employee Id is in range of 104 to 110

· Fetch Record where Employee Id should not be in range 104 to 110

· Fetch Record where employee Salary in Range of 10000 and 30000

· Fetch employee Name – who belongs to IT, EDU, HR

· Fetch employee Name – who belongs to not in IT, EDU, HR

SESSION – 4

· Create a table employee with column and datatype and constraint:

COLUMN NAME	DATA TYPE	CONSTRAINT
ID	INT	PRIMARY KEY
NAME	VARCHAR (50)	NOT NULL
ADDRESS	VARCHAR (50)	NOT NULL
SALARY	MONEY	NOT NULL, IN 20,000-50,000
GENDER	CHAR (1)	NOT NULL, (M/F/O)
DEPARTMENT	VARCHAR (50)	NOT NULL, (IT/MGT/HR)
DESIGNATION	VARCHAR (50)	NOT NULL, (DEVELOPER/MANAGER/EXECUTIVE)

SESSION – 5

· Create database record

· Create two tables student details and student fees with column name and constraint given below :-

STU_DETAILS

COLUMN NAME	DATA TYPE	CONSTRAINT
ID	INT	PRIMARY KEY
NAME	VARCHAR (50)	NOT NULL
ADDRESS	VARCHAR (50)	NOT NULL
MOBILE	VARCHAR (10)	NOT NULL

STU_FEE

COLUMN NAME	DATA TYPE	CONSTRAINT
ID	INT	FOREIGN KEY REF STU_D%TL
NAME	VARCHAR (50)	NOT NULL
FEES	MONEY	NOT NULL 10,000 – 25,000
DOS ( DATE OF SUBMIT )	DATE TIME	DEFAULT

SESSION – 6

Consider the EMP and DEPT tables for the following queries :-

· Retrieve a list of MANAGERS.

· Find out salary of both MILLER and SMITH.

· Find out the names and salaries of all employees earning more than 1000 per month.

· Display the names and salaries of all employees except JAMES.

· Find out the details of employees whose names begin with ‘S’.

· Find out the names of all employees that have ‘A’ anywhere in their name.

· Find out the names of all employees that have ‘L’ as their third character in their name.

· Find out the names of the employees whose name begin with ‘A’ or ‘M’.

· Compute yearly salary of SMITH.

· Compute daily salary of JONES.

· Calculate the total monthly salary of all employees.

· Print the average annual salary.

· Select the name, job, salary, department number of all employees except SALESMAN from department number 30.

· List unique departments of the EMP table.

SELECT DISTINCT DEPT_NO FROM EMP

DEPT_NO

· Print the name and average salary of each department

· Select the minimum and maximum salary from employee table.

· Select the minimum and maximum salaries from each department in employee table.

· List the name and salary of employees who can earn more than 1500 and are in department 10 or 30. Label the columns Employee and Monthly Salary respectively.

SELECT ENAME "NAME",SAL "MONTHLY SALARY" FROM EMP WHERE SAL>1500 AND DEPTNO IN(10,30)

NAME	MONTHLY SALARY
ALLEN	1600
BLAKE	2850
CLARK	2450
KING	5000

· Select the details of employees whose salary is below 1000 and job is CLERK.

· List the name and salary for all employees whose salary is not in the range of 1500 and 2850.

SELECT ENAME, SAL FROM EMP WHERE SAL<1500 OR SAL>2850

ENAME	SAL
SMITH	800
WARD	1250
JONES	2975
MARTIN	1250
SCOTT	3000
KING	5000
ADAMS	1100
JAMES	950
FORD	3000
MILLER	1300

· Display the name and job of all employees who do not have a MANAGER.

SELECT ENAME, JOB FROM EMP WHERE JOB IN ('MANAGER','PRESIDENT')

ENAME	JOB
JONES	MANAGER
BLAKE	MANAGER
CLARK	MANAGER
KING	PRESIDENT

· Display the name, job, and salary of all the employees whose job is MANAGER or ANALYST and their salary is not equal to 1000, 3000, or 5000.

SELECT ENAME,JOB,SAL FROM EMP WHERE JOB IN('MANAGER','ANALYST') AND SAL <> 1000 AND SAL <> 2000 AND SAL <> 3000

ENAME	JOB	SAL
JONES	MANAGER	2975
BLAKE	MANAGER	2850
CLARK	MANAGER	2450

· Display the name, salary and commission for all employees whose commission amount is greater than their salary increased by 10%.

select sal,comm from emp where sal/10=comm

· Display the name of all employees who have two Ls in their name and are in department 30 or their manager is 7782.

SQL> SELECT ENAME FROM EMP WHERE ENAME LIKE ('%L%L%') AND DEPTNO = 30 OR MGR = 7782

ENAME

ALLEN

MILLER

·
Display the names of employees with experience of over 10 years or under and Count the total number of employees.

· Retrieve the names of departments in ascending order and their employees in descending order.

· Find out experience of MILLER.

· How many different departments are there in the employee table.

· Find out which employee either work in SALES or RESEARCH department.

SESSION – 7

· Create table Emp_record with following fields given below :

Apply Self join on it by following query :-

· Show the manager name of all Employees with Employees id and name using alias.

COLUMN NAME	DATA TYPE	CONSTRAINT
E_ID	CHAR (5)	PRIMARY KEY
E_NAME	VARCHAR (40)	NOT NULL
MOBILE NO	NUMERIC (10)	NOT NULL
MANAGER_ID	CHAR (5)	---------------

SESSION – 8

Common Table Expression Based Question

COLUMN NAME	DATA TYPE	CONSTRAINT
E_ID	CHAR (5)	PRIMARY KEY
E_NAME	VARCHAR (40)	NOT NULL
MOBILE NO	NUMERIC (10)	NOT NULL
MANAGER_ID	CHAR (5)	---------------
Dept	Varchar(20)	NOT NULL
SALARY	MONEY	Check , it should be >25000

1. Find Out Highest & Minimum Salary From the the Above Listed Table employee

2. Find out first Highest salary along with its Detail

3. Find out 3^rd Highest Salary along with its complete Detail

4. Find out Minimum Salary with its Details.

5. List Total Number of Employee Working in Each Dept.

6. List Total Number of Employee working in Each dept with Total sum of amount.

List total Number of employee working in Each dept but Total Employee should be more than 5

IT TECHNOLOGY - FUNDAMENTAL LOGIC BUILDING GROUP.

Microsoft SQL Server