4 - MySql Practical for class 12 CS and IP

 Write the sort notes on group by, having and where clause with example.

WHERE Clause:

WHERE Clause is used to filter the records/rows from the table. It can be used with SELECT, UPDATE, DELETE statements.

Select * from employees where salary>25000;

Group by Clause:

The GROUP BY clause is used with aggregate functions (MAX, SUM, AVG, COUNT, MIN) to group the results by one or more columns i.e. The GROUP BY clause is used with the SELECT statement to arrange required data into groups. 

The GROUP BY statement groups rows that have the same values. This Statement is used after the where clause. This statement is often used with some aggregate function like SUM, AVG, and COUNT etc. to group the results by one or more columns.

SELECT COUNT (City) AS COUNT_CITIES, City FROM EMPLOYEES GROUP BY City;

Having Clause:

Having Clause is like the aggregate function with the GROUP BY clause. The HAVING clause is used instead of WHERE with aggregate functions. While the GROUP BY Clause group rows that have the same values into rows. The having clause is used with the where clause in order to find rows with certain conditions. The having clause is always used after the group by clause.

SELECT COUNT (City) AS COUNT_CITIES, City FROM EMPLOYEES

GROUP BY City HAVING COUNT (City) > 1;

WHERE Clause	Having Clause	Group By Clause
WHERE Clause is used to filter the records from the table based on the specified condition.	HAVING Clause is used to filter record from the groups based on the specified condition.	The group by clause is used to group the data according to particular column or row.
WHERE Clause can be used without GROUP BY Clause	HAVING Clause cannot be used without GROUP BY Clause	Group by can be used without having clause with the select statement.
WHERE Clause implements in row operations	HAVING Clause implements in column operation	It groups the output on basis of some rows or columns.
WHERE Clause cannot contain aggregate function	The having clause can contain aggregate functions.	It cannot contain aggregate functions.
WHERE Clause is used with single row function like UPPER, LOWER etc.	HAVING Clause is used with multiple row function like SUM, COUNT etc	Group by Clause is a multiple row function
WHERE Clause can be used with SELECT, UPDATE, DELETE statement.	HAVING Clause can only be used with SELECT statement.	The GROUP BY clause is used in the SELECT statement.
WHERE Clause is used before GROUP BY Clause	HAVING Clause is used after GROUP BY Clause	Group By is used after the Where clause and before the HAVING Clause

3- Mysql Practical for class 12 CS and IP

 

 Write the difference between drop, delete and truncate command with example.

Drop:

Drop is a DDL (Data Definition Language) command of SQL.

Drop is used to remove an object completely just like remove database, table, view, index etc.

Syntax:

DROP object object_name

Examples:

DROP TABLE table_name;

table_name: Name of the table to be deleted.

DROP DATABASE database_name;

database_name: Name of the database to be deleted.

Delete:

Delete is a DML (Data Manipulation Language) command of SQL.

Delete is used to remove one or more rows from a table, view, index etc. We can use where clause with delete command. Delete remove one row at a time i.e. it remove rows one by one. It will not delete columns or structure of table.

Syntax:

delete from table_name where condition;

Examples:

delete from table_name;

table_name: Name of the table from which rows will be deleted.

Delete all rows from the table.

delete from table_name where condition;

delete specific rows on the bases of where clause condition.

Truncate:

Truncate is a DDL (Data Defination Language) command of SQL.

Truncate is used to remove all rows from a table, view, index etc. We can not use where clause with truncate command. Truncate remove all rows at once, but it will not delete structure of the table or any object.

Syntax:

delete from table_name where condition;

Examples:

trucate table_name;

table_name: Name of the table from which rows will be deleted.

Delete all rows from the table at once.

11 - MySql Practical for class 12 CS and IP

 

11. Write a SQL to Enter 5 Employee data in a single query in the table Emp (eid, ename, salary, city, dob).

10 - MySql Practical for class 12 CS and IP

 

10. Write the SQL Query for full outer join of two tables Emp(eid, ename, salary,dept_id) and Dept(dept_id, dname)

 

9 - MySql Practical for class 12 CS and IP

 

9. Write the SQL Query for inner join of two tables Emp(eid, ename, salary,dept_id) and Dept(dept_id, dname)

 

8 - MySql Practical for class 12 CS and IP

 

8.  Write the SQL Query to increase 20% salary of the employee whose experience is more than 5 year of the table Emp(id, name, salary, exp)

 

7 - MySql Practical for class 12 CS and IP

 

7.       Write the SQL Query to display the difference of highest and lowest salary of each department having maximum salary greater than 4000.

 

6 - MySql Practical for class 12 CS and IP

 

6.       Shiva is using a table employee. It has following details: Employee(Code, Name, Salary, DeptCode). Write the SQL query to display maximum salary department wise.

 

5 - MySql Practical

         

5.       Write the SQL query to find out the square root of 26.

 

2 - MySql Practical

         

2.       Shiva is using a table with the following details:

Students(Name, Class, Stream_id, Stream_Name)

Write the SQL query to display the names of students who have not been assigned any stream or have been assigned Stream_Name that end with “computers”