Important SQL Queries in SQL Part 6 #DOTC_Mdb

Important SQL Queries in SQL Part 6.

In my previous blogs I discussed about several SQL queries which commonly used in SQL and today again I will discuss about some important queries in SQL . Before that I suggest you to go through my SQL and PL/SQL blogs in archive May and June folder for your understanding.

 

I suggest you before Starting the queries please go through my blog Sub query /Co-related sub query and different logical operator for your understanding.

1.  Co- Related Sub Query Example with other ALL operator : Let’s take an example for a typical sub query using ALL operator. In this example, we need to return the names of employee from department table  whose salary is greater than the salary of all the employees in department 100.

 

Below is the Query :

SELECT emp_name

FROM department

WHERE Salary > ALL ( SELECT Salary

FROM department

WHERE Dept_no=100 );

 

Below is the output of above query:

 

 

 

2. Find the manager_ID whose maximum salary among all employees of that manager_id  is greater than salary average  of all manager_id.

 

Below is the Query for that :

 

SELECT manager_id

FROM department

GROUP BY dept_no

HAVING max(salary) > ALL (SELECT avg(salary)

                            FROM department

                            GROUP BY dept_no);

 

Output of the above Query is as below :

3 . Using Any Operator : Find the Distinct dept_no of the emp_id which have any record in Department Table and location not in Delhi and Chennai.

 

SELECT DISTINCT dept_no

FROM department

WHERE emp_id = ANY (SELECT emp_id

 FROM department

 where Location NOT IN( 'DELHI','Chennai'));

Below is the output of above query :

 

Here we see all the distinct dept_no apart from 100 as in outer query we put location not belongs to DELHI and Chennai as both belongs to dept_no 100

Note : You can write query against your requirement by taking reference from this queries.

4 . How to add / modify / Delete column in existing table .

To add column in existing table we can do it by using ALTER DDL statement.

Suppose I want to add last name is existing table department then below statement we can use.

ALTER TABLE DEPARTMENT ADD COLUMN LAST_NAME VARCHAR(50);

To Modify the data type of column we can also use the Alter statement as below :

ALTER TABLE DEPARTMENT MODIFY LAST_NAME VARCHAR(40);

See the output of above statements.

 

Here you can see that one column is added and second command modify the data type of column from VARCHAR(50) to VARCHAR(40).

In same Manner you can Drop the column by using ALTER statement.

ALTER TABLE DEPARTMENT DROP COLUMN LAST_NAME;

5 . How to Add constraint like Primary Key / Foreign Key NOT NULL in Existing table.

 

To add primary key in existing table we use below DDL statement :

ALTER TABLE STUDENT ADD PRIMARY KEY (STUD_ID);

To add Foreign Key we use below statement in existing table :

ALTER TABLE STUDENT_DETAILS ADD FOREIGN KEY (STUD_ID) REFERENCES STUDENT (STUD_ID);

Below is the output of above statement.

 

Note :we created constraint Primary key on stud_id in Student table , So in Sudent table for stud_id we cannot insert any duplicate values as well as NULL values also we created foreign key on stud_id in student_details table . so in student_details table we can insert multiple values which exist in primary table but unable to insert any other record . We can say this as Parent child relations. If you want to explore more on this please follow my previous blog for more details .

ADDING NOT NULL CONSTRAINT IN EXISTING TABLE :

ALTER TABLE STUDENT MODIFY COLUMN STUDENT_ADDRESS VARCHAR(60) NOT NULL;

Now After issuing this command user cannot enter any null values.

Please go through this blog carefully and try to write as many queries as you can and also use constraint to restrict the user as per your requirement and let me know if you have any doubts. I will suggest you to please go through my previous blogs related to SQL topics for your reference.

Thanks.