DBMS LAB Manual

(DBMS: Database language-DML, DDL, DCL, TCL (write the theory and syntax for each command
present in DDL, DML, DCL, TCL)):

DDL:
Aim: To understand the syntax of basic DDL commands such as

o Create table
o Alter table
 Add/Drop attribute
 Modify type and size of an attribute
 Rename attribute
o Truncate
o Drop
Database used: Student
Table name: student
Students(rollno,name)Add branch
Modify the width of Attribute branch
Modify the datatype of Attribute
branch Rename the attribute
Remove the attribute

mysql> create table students (rollno int,name char(20));

mysql> desc students;

mysql> alter table students add branch char(15);

mysql> desc students;

mysql> alter table students modify branch char(10);

mysql> desc students;

mysql> alter table students modify branch varchar(10);

mysql> desc students;

mysql> alter table students change name sname char(20);

mysql> desc students;

mysql> alter table students drop branch;

mysql> desc students;

mysql> truncate table

students; mysql> drop table

students;
DML

Aim: To understand the syntax of basic DML commands such as

o Insert
o Update
o Delete
Database used: Student

mysql> create table students (rollno int, name char (20), branch varchar (20));

mysql> insert into students values(1,'saketh','cse');

mysql> insert into students values(2,'arun','it'),(3,'varun','eee');

mysql> insert into students values(4,'sathish','it'),(4,'sridhar','eee');

mysql> select * fromstudents;

mysql> update students set name='venkat' where rollno=2;

mysql> select * fromstudents;

mysql> update students set name='Vinod’, branch='ece' where rollno=3;

mysql> select * fromstudents;

mysql> update students set name='vamshi' where rollno=4;

mysql> select * from students;

mysql> delete from students where rollno=1;

mysql> select * from students;

mysql> update students set

branch='cse'; mysql> select * from

students;

mysql> delete from students;

Constraints
Aim: Objective to understand the commands used to impose constraints on a table such as

o Not null
o unique
o primary key
o foreign key
Database used: Bank
Branch ()
Account ()
Loan ()
Customer ()
Depositor ()
Borrower ()

mysql> create table Branch (branch_name varchar (15) , branch city varchar (15) not null,assets
integer not null, primary key(branch_name));

mysql> create table Account (account_number varchar (15), branch_name varchar (15) not
null,balance integer not null, primary key(account_number), foreign key(branch_name)
references Branch(branch_name));

mysql> create table Customer (customer_name varchar(15),customer_street varchar(12)not

null,customer_city varchar(15) not null, customer_ph varchar(10) unique, primary
key(customer_name));

mysql> create table Depositor (customer_name varchar(15),account_number varchar(15),primary

key(customer_name, account_number),foreign key(account_number) references
Account(account_number), foreign key(customer_name) references
Customer(customer_name));

mysql> create table Borrower (customer_name varchar (15), loan_number varchar(15),primary

key (customer_name, loan_number), foreign key(customer_name) references
Customer(customer_name), foreign key(loan_number) references Loan(loan_number));

Adding primary key constraint.

mysql> Create table cust(cust_no integer(10),cust_name varchar(20));

mysql> desc cust;

mysql> Alter table cust add primary key(cust_no);

mysql> desc cust;

Adding foreign key constraint.

mysql> create table cust1(cust_no int(10),cust_name varchar(20));

mysql> desc cust1;

mysql> alter table cust1 add foreign key (cust_no) references

cust(cust_no); mysql> desc cust1;

REMOVING A PRIMARY KEY CONSTRAINT

mysql> alter table cust drop primary

key; mysql> desc cust;

Exp No:1

AIM: Write the basic structure of SQL query with example.

(Only syntax)

create database ishika_file;

Query OK, 1 row affected (0.03 sec)

mysql> use ishika_file;

Database changed

mysql> create table bank(bank_name varchar(20),bank_id int,amt int);

Query OK, 0 rows affected (0.10 sec)

mysql> insert into bank values("kedia palace",42,50000);

Query OK, 1 row affected (0.03 sec)

mysql> insert into bank values("sitapura",02,4000);

Query OK, 1 row affected (0.01 sec)

mysql> insert into bank values("vaishali",24,24000);

Query OK, 1 row affected (0.01 sec)

mysql> select * from bank;

+ +- + +
| bank_name | bank_id | amt |
+ +- + +
| kedia palace | 42 | 50000 |
| sitapura | 2 | 4000 |
| vaishali | 24 | 24000 |
+ +- + +
3 rows in set (0.01 sec)

mysql> select amt from bank where bank_name='kedia palace';

+ +
| amt |
+ +
| 50000 |
+ +
1 row in set (0.01 sec)
Exp No: 2

AIM: Analyze the problem and come with the entities in it.

(Write down what is ER Diagram, Components of ER Diagram in detail with 1 example.

Here take banking database and draw only ER Diagram)

An Entity-Relationship (ER) diagram is a type of data modeling technique that

helps in designing and developing a database. It is a graphical representation of
the entities, attributes, and the relationships among them.

The components of an ER diagram are:

Entity: An entity is a real-world object or concept that is identifiable and

distinct from other objects. It is represented by a rectangle in an ER diagram.
For example, in a student management system, entities can be students, teachers,
courses, etc.

Attribute: An attribute is a property or characteristic of an entity. It is

represented by an oval shape inside an entity rectangle. For example, attributes
of a student entity can be name, age, address, etc.

Relationship: A relationship is a connection or association between entities. It

is represented by a diamond shape in an ER diagram. For example, in a student
management system, a relationship can exist between a student and a course.

Cardinality: Cardinality defines the number of instances of an entity that can be

associated with the instances of another entity through a relationship. It is
represented by a number or a symbol inside the diamond shape. For example, a
student can enroll in one or many courses, and a course can be taken by many or
one student.

Optional or Mandatory Participation: Optional participation is when an instance of

an entity can exist without being associated with any instance of the related
entity. It is represented by an open circle at the end of the relationship line.
Mandatory participation is when an instance of an entity must be associated with
at least one instance of the related entity. It is represented by a solid circle
at the end of the relationship line.

Weak Entity: A weak entity is an entity that cannot be uniquely identified by its
attributes alone. It depends on the existence of a related entity. It is
represented by a double rectangle in an ER diagram.
Exp No: 3

AIM: Represent all the entities in tabular fashion for Banking Database

(Tables with maximum 2 entries with output i.e. table diagram using SQL queries)

mysql> create database bank;

Query OK, 1 row affected (0.01 sec)

mysql> use bank;

Database changed
mysql> create table branch(branch_name varchar(20),b_city char(10));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into branch values("sitapura","jaipur");

Query OK, 1 row affected (0.02 sec)

mysql> insert into branch values("vaishali","delhi");

Query OK, 1 row affected (0.01 sec)

mysql> select * from branch;

+ -+ +
| branch_name | b_city |
+ -+ +
| sitapura | jaipur |
| vaishali | delhi |
+ -+ +
2 rows in set (0.00 sec)

mysql> create table account(acc_no int,branch_name varchar(40));

Query OK, 0 rows affected (0.03 sec)

mysql> insert into account values(02,"sitapura"),(04,"vaishali");

Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from account;

+ + +
| acc_no | branch_name |
+ + +
| 2 | sitapura |
| 4 | vaishali |
+ + +
2 rows in set (0.00 sec)

mysql> create table depositor(cust_id int,acc_no int);

Query OK, 0 rows affected (0.03 sec)

mysql> insert into depositor values(49,02),(92,04);

Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from depositor;
+ -+ +
| cust_id | acc_no |
+ -+ +
| 49 | 2 |
| 92 | 4 |
+ -+ +
2 rows in set (0.00 sec)

mysql> create table customer(cust_id int,cust_name varchar(40));

Query OK, 0 rows affected (0.03 sec)

mysql> insert into customer values(49,"ishika"),(92,"muskan");

Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from customer;

+ -+ -+
| cust_id | cust_name |
+ -+ -+
| 49 | ishika |
| 92 | muskan |
+ -+ -+
2 rows in set (0.00 sec)

mysql> create table borrower(cust_id int,loan_no int);

Query OK, 0 rows affected (0.03 sec)

mysql> insert into borrower values(49,040),(92,924);

Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from borrower;

+ -+ +
| cust_id | loan_no |
+ -+ +
| 49 | 40 |
| 92 | 924 |
+ -+ +
2 rows in set (0.00 sec)

mysql> create table loan(loan_no int,branch_name varchar(40));

Query OK, 0 rows affected (0.03 sec)

mysql> insert into loan values(040,"sitapura"),(924,"vaishali");

Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from loan;

+ -+ +
| loan_no | branch_name |
+ -+ +
| 40 | sitapura |
| 924 | vaishali |
+ -+ +
2 rows in set (0.00 sec)
Exp No:4

AIM: Create a Customer Database Table and insert datas using all the SQL queries

(Create, insert, update, delete, drop, alter with output i.e. table diagram)

mysql> create database customer;

Query OK, 1 row affected (0.01 sec)

mysql> use customer;

Database changed
mysql> create table loan(loan_no int,loan_branch varchar(40));
Query OK, 0 rows affected (0.02 sec)

mysql> alter table loan add(loan_amt int,loan_type varchar(40));

Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc loan;

+ -+ + +-- +- + +
| Field | Type | Null | Key | Default | Extra |
+ -+ + +-- +- + +
| loan_no | int | YES | | NULL | |
| loan_branch | varchar(40) | YES | | NULL | |
| loan_amt | int | YES | | NULL | |
| loan_type | varchar(40) | YES | | NULL | |
+ -+ + +-- +- + +
4 rows in set (0.03 sec)

mysql> insert into loan values(01,"sitapura",40000,"home");

Query OK, 1 row affected (0.01 sec)

mysql> insert into loan values(02,"kedia palace",50000,"car");

Query OK, 1 row affected (0.01 sec)

mysql> insert into loan values(03,"vaishali",60000,"phone");

Query OK, 1 row affected (0.01 sec)

mysql> insert into loan values(04,"murlipura",70000,"study");

Query OK, 1 row affected (0.01 sec)

mysql> select * from loan;

+ -+ + +- --+
| loan_no | loan_branch | loan_amt | loan_type |
+ -+ + +- -+
| 1 | sitapura | 40000 | home |
| 2 | kedia palace | 50000 | car |
| 3 | vaishali | 60000 | phone |
| 4 | murlipura | 70000 | study |
+ -+ + +- -+
4 rows in set (0.00 sec)

mysql> update loan set loan_amt=55000 where loan_type =

"car"; Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from loan;
+ -+ + +- -+
| loan_no | loan_branch | loan_amt | loan_type |
+ -+ + +- -+
| 1 | sitapura | 40000 | home |
| 2 | kedia palace | 55000 | car |
| 3 | vaishali | 60000 | phone |
| 4 | murlipura | 70000 | study |
+ -+ + +- -+
4 rows in set (0.00 sec)

mysql> alter table loan add loan_month varchar(20);

Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from loan;

+ -+ + +- -+ +
| loan_no | loan_branch | loan_amt | loan_type | loan_month |
+ -+ + +- -+ +
| 1 | sitapura | 40000 | home | NULL |
| 2 | kedia palace | 55000 | car | NULL |
| 3 | vaishali | 60000 | phone | NULL |
| 4 | murlipura | 70000 | study | NULL |
+ -+ + +- -+ +
4 rows in set (0.00 sec)

mysql> delete from loan where loan_type="car";

Query OK, 1 row affected (0.01 sec)

mysql> select * from loan;

+ -+ + +-- + -+
| loan_no | loan_branch | loan_amt | loan_type | loan_month |
+ -+ + +-- + -+
| 1 | sitapura | 40000 | home | NULL |
| 3 | vaishali | 60000 | phone | NULL |
| 4 | murlipura | 70000 | study | NULL |
+ -+ + +-- + -+
3 rows in set (0.00 sec)

mysql> alter table loan drop loan_month;

Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from loan;

+ -+ + +-- +
| loan_no | loan_branch | loan_amt | loan_type |
+ -+ + +-- +
| 1 | sitapura | 40000 | home |
| 3 | vaishali | 60000 | phone |
| 4 | murlipura | 70000 | study |
+ -+ + +-- +
3 rows in set (0.00 sec)
Exp No:5

AIM:Create tables using following constraints: integrity constraint and domain

constraint for Bank database using customer, account and depositor table

(Tables with maximum 2 entries with syntax, query and output i.e. table diagram)

mysql> create database banking;

Query OK, 1 row affected (0.01 sec)

mysql> use banking;

Database changed

mysql> create table account(acc_no int PRIMARY KEY,acc_type varchar(10) NOT NULL
CHECK(acc_type IN('D','C')),bal int CHECK(bal>=0));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into account values(04,'D',4000);

Query OK, 1 row affected (0.01 sec)

mysql> insert into account values(06,'C',50000);

Query OK, 1 row affected (0.01 sec)

mysql> select * from account;

+ + + +
| acc_no | acc_type | bal |
+ + + +
| 4 | D | 4000 |
| 6 | C | 50000 |
+ + + +
2 rows in set (0.00 sec)

mysql> create table customer(cust_id int primary key,cust_name varchar(50) not

null,address varchar(100) not null,cust_no int not nul
l);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into customer values(43,"ishika","kedia palace");

ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into customer values(43,"ishika","kedia palace",40);
Query OK, 1 row affected (0.01 sec)

mysql> insert into customer values(92,"khushi","vaishali nagar",9);

Query OK, 1 row affected (0.01 sec)

mysql> select * from customer;

+ -+ -+ --+ +
| cust_id | cust_name | address | cust_no |
+ -+ -+ --+ +
| 43 | ishika | kedia palace | 40 |
| 92 | khushi | vaishali nagar | 9 |
+ -+ -+ --+ +
2 rows in set (0.00 sec)
mysql> create table depositor(cust_id int not null,acc_no int not null,primary
key(cust_id,acc_no),foreign key(cust_id) references cu
stomer(cust_id));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into depositor values(43,04);

Query OK, 1 row affected (0.02 sec)

mysql> insert into depositor values(92,06);

Query OK, 1 row affected (0.01 sec)

mysql> select * from depositor;

+ -+ +
| cust_id | acc_no |
+ -+ +
| 43 | 4 |
| 92 | 6 |
+ -+ +
2 rows in set (0.00 sec)
Exp No:6

AIM:Write SQL query using Aggregate Functions for account table

(MIN, MAX, COUNT, SUM, AVG-write theory about aggregate functions with syntax of
each and its use)
(5 to 6 entries in account table with 3 attributes)

Questions to find out queries:

1. Write a query to find the total balance of the account
2. Write a query to find the average balance of
the account And so on….

Aggregate functions are used to perform calculations on a set of values and return a single
result. They are commonly used in SQL queries to summarize data and provide insights into
large datasets. The most commonly used aggregate functions are MIN, MAX, COUNT,
SUM, and AVG.

1) MIN: The MIN function returns the minimum value in a set of values. The syntax for
the MIN function is:
(1) SELECT MIN(column_name)
(2) FROM table_name;
2) MAX: The MAX function returns the maximum value in a set of values. The syntax
for the MAX function is:
i) SELECT MAX(column_name)
ii) FROM table_name;
3) COUNT: The COUNT function returns the number of rows in a table or the number
of values in a specified column. The syntax for the COUNT function is:
i) SELECT COUNT(*|column_name)
ii) FROM table_name;
4) SUM: The SUM function returns the sum of all the values in a specified column.
The syntax for the SUM function is:
i) SELECT SUM(column_name)
ii) FROM table_name;

5) AVG: The AVG function returns the average value of a specified column. The syntax
for the AVG function is:
i) SELECT AVG(column_name)
ii) FROM table_name;
mysql> create database aggregate;
Query OK, 1 row affected (0.01 sec)
mysql> use aggregate;
Database changed
mysql> create table account(acc_no int, acc_branch varchar(40),bal int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into account values(01,"Murlipura",4000);

Query OK, 1 row affected (0.02 sec)

mysql> insert into account values(02,"Sitapura",2000);

Query OK, 1 row affected (0.01 sec)

mysql> insert into account values(03,"durgapura",1500);

Query OK, 1 row affected (0.01 sec)

mysql> insert into account values(04,"vaishali",5000);

Query OK, 1 row affected (0.01 sec)

mysql> insert into account values(05,"jagatpura",3000);

Query OK, 1 row affected (0.01 sec)

mysql> select * from account;

+ + + -+
| acc_no | acc_branch | bal |
+ + + -+
| 1 | Murlipura | 4000 |
| 2 | Sitapura | 2000 |
| 3 | durgapura | 1500 |
| 4 | vaishali | 5000 |
| 5 | jagatpura | 3000 |
+ + + -+
5 rows in set (0.00 sec)

mysql> select MIN(bal) from account;

+ +
| MIN(bal) |
+ +
| 1500 |
+ +
1 row in set (0.02 sec)
mysql> select MAX(bal) from account;
+ +
| MAX(bal) |
+ +
| 5000 |
+ +
1 row in set (0.00 sec)

mysql> select COUNT(bal) from account;

+ +
| COUNT(bal) |
+ +
| 5|
+ +
1 row in set (0.00 sec)

mysql> select SUM(bal) from account;

+ +
| SUM(bal) |
+ +
| 15500 |
+ +
1 row in set (0.00 sec)

mysql> select AVG(bal) from account;

+ -+
| AVG(bal) |
+ -+
| 3100.0000 |
+ -+
1 row in set (0.00 sec)
Exp No:7

AIM:Write SQL query using Logical Operators for customer table

(AND, OR, NOT, NOR, BETWEEN, IN, LIKE {use all like operation symbols},
EXISTS, ALL, ANY- write theory about Logical operators with syntax of each and its
use)
(5 to 6 entries in customer table)

Logical operators are used in SQL to combine or negate multiple conditions in a query. The
most commonly used logical operators in SQL are AND, OR, NOT, NOR, BETWEEN, IN,
LIKE, EXISTS, ALL, and ANY.

1. AND: The AND operator returns true if all conditions in a query are true. The syntax
for the AND operator is:

SELECT column1, column2, ...

FROM table_name
WHERE condition1 AND condition2 AND ...;

For example, to select all customers from a table called "Customers" who are
from the USA and have a "Premium" membership, you would use the following
query:

SELECT * FROM Customers

WHERE country = 'USA' AND membership = 'Premium';

2. OR: The OR operator returns true if any of the conditions in a query are true. The syntax
for the OR operator is:

SELECT column1, column2, ...

FROM table_name
WHERE condition1 OR condition2 OR ...;

For example, to select all customers from a table called "Customers" who are from
the USA or have a "Premium" membership, you would use the following query:

SELECT *
FROM Customers
WHERE country = 'USA' OR membership = 'Premium';

3. NOT: The NOT operator negates a condition in a query. The syntax for the NOT
operator is:
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

For example, to select all customers from a table called "Customers" who are not from
the USA, you would use the following query:

SELECT *
FROM Customers
WHERE NOT country = 'USA';

4. NOR: The NOR operator returns true only if none of the conditions in a query are
true. The syntax for the NOR operator is:

SELECT column1, column2, ...

FROM table_name
WHERE NOT (condition1 OR condition2 OR ...);

For example, to select all customers from a table called "Customers" who are not from
the USA or have a "Premium" membership, you would use the following query:

SELECT *
FROM Customers
WHERE NOT (country = 'USA' OR membership = 'Premium');

5. BETWEEN: The BETWEEN operator selects values within a range. The syntax for
the BETWEEN operator is:

SELECT column1, column2, ...

FROM table_name
WHERE column_name BETWEEN value1 AND value2;

For example, to select all orders from a table called "Orders" with order dates between
January 1, 2022, and December 31, 2022, you would use the following query:

SELECT *
FROM Orders
WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31';

6. IN: The IN operator selects values that match any of a specified list of values. The
syntax for the IN operator is:
SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);

For example, to select all customers from a table called "Customers" who are from the
USA, Canada, or Mexico, you would use the following query:

SELECT *
FROM Customers
WHERE country IN ('USA', 'Canada', 'Mexico');

7. LIKE: The LIKE operator selects values that match a specified pattern using wildcard
characters.

mysql> create database logical;

Query OK, 1 row affected (0.02 sec)

mysql> use logical;

Database changed
mysql> create table customer(cust_id int,cust_name varchar(40),cust_city
varchar(30));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into customer values(01,"ishika","jaipur");

Query OK, 1 row affected (0.02 sec)

mysql> insert into customer values(02,"honey","udaipur");

Query OK, 1 row affected (0.01 sec)

mysql> insert into customer values(03,"muskan","ajmer");

Query OK, 1 row affected (0.01 sec)

mysql> insert into customer values(04,"pranjali","mumbai");

Query OK, 1 row affected (0.01 sec)

mysql> insert into customer values(05,"harshita","amer");

Query OK, 1 row affected (0.01 sec)
mysql> select * from customer;
+ -+ + -+
| cust_id | cust_name | cust_city |
+ -+ + -+
| 1 | ishika | jaipur |
| 2 | honey | udaipur |
| 3 | muskan | ajmer |
| 4 | pranjali | mumbai |
| 5 | harshita | amer |
+ -+ + -+
5 rows in set (0.00 sec)

mysql> select cust_id from customer where cust_name="ishika" AND

cust_city="jaipur";
+ -+
| cust_id |
+ -+
| 1|
+ -+
1 row in set (0.00 sec)

mysql> select cust_id from customer where cust_name="ishika" OR

cust_city="udaipur";
+ -+
| cust_id |
+ -+
| 1|
| 2|
+ -+
2 rows in set (0.00 sec)

mysql> select cust_id from customer where NOT cust_city="jaipur";

+ -+
| cust_id |
+ -+
| 2|
| 3|
| 4|
| 5|
+ -+
4 rows in set (0.00 sec)

mysql> select cust_id from customer where NOT (cust_city="jaipur" OR

cust_name="ishika");
+ -+
| cust_id |
+ -+
| 2|
| 3|
| 4|
| 5|
+ -+
4 rows in set (0.00 sec)

mysql> select cust_id from customer where cust_id BETWEEN 02 AND 04;
+ -+
| cust_id |
+ -+
| 2|
| 3|
| 4|
+ -+
3 rows in set (0.01 sec)

mysql> select cust_id from customer where cust_city IN("jaipur","udaipur");

+ -+
| cust_id |
+ -+
| 1|
| 2|
+ -+
2 rows in set (0.01 sec)

mysql> select cust_id from customer where cust_name LIKE 'M%';

+ -+
| cust_id |
+ -+
| 3|
+ -+
1 row in set (0.01 sec)
mysql> select cust_id from customer where EXISTS (select cust_name from customer
where cust_id=03);
+ -+
| cust_id |
+ -+
| 1|
| 2|
| 3|
| 4|
| 5|
+ -+
5 rows in set (0.00 sec)

mysql> select cust_id from customer where cust_id>ALL(select cust_id from customer
where cust_city="ajmer");
+ -+
| cust_id |
+ -+
| 4|
| 5|
+ -+
2 rows in set (0.01 sec)

mysql> select cust_id from customer where cust_id>ANY(select cust_id from

customer where cust_name="ishika");
+ -+
| cust_id |
+ -+
| 2|
| 3|
| 4|
| 5|
+ -+
4 rows in set (0.00 sec)
Exp No:8

AIM: Write SQL query using Group by Function, Having and Order By Function

(Theory, Syntax and Query)

Using GROUP BY function:

Suppose you have a table called "Orders" with columns "OrderID", "CustomerID",
"OrderDate", and "TotalAmount". To get the total amount of orders for each customer, you
can use the GROUP BY function as follows:
SELECT CustomerID,
SUM(TotalAmount) FROM Orders
GROUP BY CustomerID;
This query will group the orders by customer ID and calculate the total amount of orders
for each customer.

Using HAVING function:

Continuing with the previous example, let's say you only want to show the results for
customers who have spent more than $1000 in total. You can use the HAVING function as
follows:
SELECT CustomerID,
SUM(TotalAmount) FROM Orders
GROUP BY CustomerID
HAVING SUM(TotalAmount) > 1000;
This query will group the orders by customer ID, calculate the total amount of orders for
each customer, and then only show the results for customers who have spent more than
$1000 in total.

Using ORDER BY function:

Suppose you have a table called "Employees" with columns "EmployeeID", "FirstName",
"LastName", "Department", and "Salary". To show the list of employees sorted by
department and salary, you can use the ORDER BY function as follows:
SELECT EmployeeID, FirstName, LastName, Department, Salary
FROM Employees
ORDER BY Department, Salary DESC;
This query will select all the employees from the "Employees" table and sort them first by
department (in ascending order) and then by salary (in descending order).
mysql> create table dataset;
ERROR 4028 (HY000): A table must have at least one visible column.
mysql> create table dataset(cust_id int,cust_name varchar(40),cust_city
varchar(30),cust_age int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into dataset values(01,"ishika","jaipur",19);

Query OK, 1 row affected (0.00 sec)

mysql> insert into dataset values(02,"asha","udaipur",16);

Query OK, 1 row affected (0.00 sec)

mysql> insert into dataset values(03,"pranchal","ajmer",21);

Query OK, 1 row affected (0.00 sec)

mysql> select cust_city from customer group by cust_id having cust_age>18;

mysql> select * from dataset;

+ -+ + -+- -+
| cust_id | cust_name | cust_city | cust_age |
+ -+ + -+- -+
| 1 | ishika | jaipur | 19 |
| 2 | asha | udaipur | 16 |
| 3 | pranchal | ajmer | 21 |
+ -+ + -+- -+
3 rows in set (0.00 sec)

mysql> select cust_city from customer order by cust_age;