0% found this document useful (0 votes)

23 views32 pages

Document Sid

okay

Uploaded by

Ritesh Kumar

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as DOCX, PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

23 views32 pages

Document Sid

okay

Uploaded by

Ritesh Kumar

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as DOCX, PDF, TXT or read online on Scribd

You are on page 1/ 32

EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems

Lab 1, 2 & 3
Theory
The CREATE DATABASE statement is used to create a new SQL database.
The CREATE TABLE statement is used to create a new table in a database.
The DESC command is used to sort the data returned in descending order.
The INSERT INTO statement is used to insert new records in a table.
The SELECT statement is used to select data from a database.
The UPDATE statement is used to modify the existing records in a table.
The SELECT DISTINCT statement is used to return only distinct (different) values.
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
MySQL's aggregate function is used to perform calculations on multiple values and return the result in
a single value like the average of all values count(): It returns the number of rows, including
rows with NULL values in a group. sum(): It returns the total summed values (Non-NULL) in a
set.
average(): It returns the average value of an expression.
min(): It returns the minimum (lowest) value in a set.
max(): It returns the maximum (highest) value in a set.
The DELETE statement is used to delete existing records in a table.
The PRIMARY KEY constraint uniquely identifies each record in a table.
Primary keys must contain UNIQUE values, and cannot contain NULL values.
A table can have only ONE primary key; and in the table, this primary key can consist of single
or multiple columns (fields).
+----------------------------------------------------------------------------+

CODE:

mysql> create database college;

Query OK, 1 row affected (0.00 sec)

mysql> use college; Database changed mysql> create table student(enrollment_num

int, name char(20), gender char(1), age int, branch char(10));
Query OK, 0 rows affected (0.01 sec)

mysql> desc student;

Page1 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
mysql> insert into student values(34, "Garvit", "M", 18, "CSE");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(44, "Aayushi", "F", 17, "EC");

Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(47, "Kratik", "M", 19, "ME");

Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(59, "Diksha", "F", 18, "AU");

Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(39, "Gaurav", "M", 18, "CSBS");

Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(67, "Mohit", "M", 19, "IT");

Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(140, "Ram Sharma", "M", 18, "CSE");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(150, "Devansh Singh", "M", 19, "ME");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(155, "Shruti Malviya", "F", 19, "CE");
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(159, "Dipika Jain", "F", 19, "IT");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(170, "Sakshi Sharma", "F", 18, "AU");
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;

+----------------+----------------+--------+------+--------+
| enrollment_num | name | gender | age | branch |
+----------------+----------------+--------+------+--------+
| 34 | Garvit | M | 18 | CSE |
| 44 | Aayushi | F | 17 | EC |
| 47 | Kratik | M | 19 | ME |
| 59 | Diksha | F | 18 | AU |
| 39 | Gaurav | M | 18 | CSBS |
| 67 | Mohit | M | 19 | IT |
| 140 | Ram Sharma | M | 18 | CSE |
| 150 | Devansh Singh | M | 19 | ME |
| 155 | Shruti Malviya | F | 19 | CE |
| 159 | Dipika Jain | F | 19 | IT |
| 170 | Sakshi Sharma | F | 18 | AU |
+----------------+----------------+--------+------+--------+

Page2 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
11 rows in set (0.01 sec)

mysql> create table faculty(Faculty_id mediumint, Name char(30), Gender char(1),

Age int, Dept varchar(10), Salary float(5, 2), Year_joined year); Query OK, 0
rows affected, 1 warning (0.02 sec)

mysql> desc faculty;

mysql> insert into faculty values(101, "Rohit Mahajan", "M", 30, "ME",
344.9,'2008');
Query OK, 1 row affected (0.01 sec)

mysql> insert into faculty values(106, "Monika Jain", "F", 25, "CSE",
548.0,'2009');
Query OK, 1 row affected (0.00 sec)

mysql> insert into faculty values(102, "Kiran Talwariya", "F", 34, "IT",
783.0,'2012');
Query OK, 1 row affected (0.00 sec)

mysql> insert into faculty values(110, "Jitendra Sharma", "M", 45, "CE",
993.0,'2005');
Query OK, 1 row affected (0.00 sec)

mysql> insert into faculty values(107, "Sheetal Agrawal", "F", 40, "AU",
678.0,'2009');
Query OK, 1 row affected (0.01 sec)

mysql> insert into faculty values(105, "Rahul Sharma", "M", 51, "ME",
693.0,'2010');
Query OK, 1 row affected (0.00 sec)
mysql> select * from faculty;
+------------+-----------------+--------+------+------+--------+-------------+
| Faculty_id | Name | Gender | Age | Dept | Salary | Year_joined |
+------------+-----------------+--------+------+------+--------+-------------+
| 101 | Rohit Mahajan | M | 30 | ME | 344.90 | 2008 |

Page3 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
| 106 | Monika Jain | F | 25 | CSE | 548.00 | 2009 |
| 102 | Kiran Talwariya | F | 34 | IT | 783.00 | 2012 |
| 110 | Jitendra Sharma | M | 45 | CE | 993.00 | 2005 |
| 107 | Sheetal Agrawal | F | 40 | AU | 678.00 | 2009 |
| 105 | Rahul Sharma | M | 51 | ME | 693.00 | 2010 |
+------------+-----------------+--------+------+------+--------+-------------+
6 rows in set (0.00 sec)

mysql> update student set gender = 'F' where name = 'Anshika Gupta';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> update student set age=19 where enrollment_num in (34, 140, 59, 170);
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0

mysql> update student set age=18 where enrollment_num not in (34, 140, 59, 170);
Query OK, 6 rows affected (0.00 sec)
Rows matched: 7 Changed: 6 Warnings: 0

mysql> update student set name = "Diksha Rathore" where name = "Diksha";
Query OK, 1 row affected (0.00 sec) Rows
matched: 1 Changed: 1 Warnings: 0

mysql> update student set name = "Neha Reddy" where name = "Neha";
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> update student set name = "Gaurav Sharma" where name = "Gaurav";
Query OK, 1 row affected (0.00 sec) Rows
matched: 1 Changed: 1 Warnings: 0

mysql> update student set name = "Garvit Paliwal" where name = "Garvit";
Query OK, 1 row affected (0.00 sec) Rows
matched: 1 Changed: 1 Warnings: 0

mysql> update student set name = "Kratik Mathur" where name = "Kratik";
Query OK, 1 row affected (0.00 sec) Rows
matched: 1 Changed: 1 Warnings: 0

mysql> update student set name = "Aayushi Talreja" where name = "Aayushi";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update student set name = "Mohit Pherwani" where name = "Mohit";
Query OK, 1 row affected (0.00 sec) Rows
matched: 1 Changed: 1 Warnings: 0

mysql> update faculty set Name = "Karan Singh" where Faculty_id = 102;

Page4 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
Query OK, 1 row affected (0.00 sec) Rows
matched: 1 Changed: 1 Warnings: 0

mysql> select distinct name from student;

mysql> select distinct name and enrollment_num from student;

+-------------------------+
| name and enrollment_num |
+-------------------------+
| 0 |
+-------------------------+ 1 row in
set, 11 warnings (0.00 sec)

mysql> select * from student where gender='M'; +----------------+----------------

+--------+------+--------+
| enrollment_num | name | gender | age | branch |
+----------------+----------------+--------+------+--------+
| 34 | Garvit Paliwal | M | 19 | CSE |
| 47 | Kratik Mathur | M | 18 | ME |
| 39 | Gaurav Sharma | M | 18 | CSBS |
| 67 | Mohit Pherwani | M | 18 | IT |
| 140 | Ram Sharma | M | 19 | CSE |
| 150 | Devansh Singh | M | 18 | ME |
+----------------+----------------+--------+------+--------+
6 rows in set (0.00 sec)
mysql> select * from student where branch='CSE'; +----------------
+----------------+--------+------+--------+
| enrollment_num | name | gender | age | branch |
+----------------+----------------+--------+------+--------+
| 34 | Garvit Paliwal | M | 19 | CSE |
| 140 | Ram Sharma | M | 19 | CSE |
+----------------+----------------+--------+------+--------+
2 rows in set (0.00 sec)

mysql> select * from student where name like 'D%'; +----------------

+----------------+--------+------+--------+
| enrollment_num | name | gender | age | branch |
+----------------+----------------+--------+------+--------+
| 59 | Diksha Rathore | F | 19 | AU |
| 150 | Devansh Singh | M | 18 | ME |
| 159 | Dipika Jain | F | 18 | IT |
+----------------+----------------+--------+------+--------+
3 rows in set (0.00 sec)

mysql> select * from student where branch like 'CSE%'; +----------------

+----------------+--------+------+--------+
| enrollment_num | name | gender | age | branch |
+----------------+----------------+--------+------+--------+
| 34 | Garvit Paliwal | M | 19 | CSE |
| 140 | Ram Sharma | M | 19 | CSE |
+----------------+----------------+--------+------+--------+
2 rows in set (0.00 sec)

mysql> select enrollment_num, name from student where branch='CSE';

+----------------+----------------+
| enrollment_num | name |
+----------------+----------------+
| 34 | Garvit Paliwal |
| 140 | Ram Sharma |
+----------------+----------------+
2 rows in set (0.00 sec)

mysql> select * from faculty where Year_joined between 2000 and 2009;
+------------+-----------------+--------+------+------+--------+-------------+ |
Faculty_id | Name | Gender | Age | Dept | Salary | Year_joined |
+------------+-----------------+--------+------+------+--------+-------------+
| 101 | Rohit Mahajan | M | 30 | ME | 344.90 | 2008 |
| 106 | Monika Jain | F | 25 | CSE | 548.00 | 2009 |
| 110 | Jitendra Sharma | M | 45 | CE | 993.00 | 2005 |
| 107 | Sheetal Agrawal | F | 40 | AU | 678.00 | 2009 |
+------------+-----------------+--------+------+------+--------+-------------+
4 rows in set (0.00 sec)

mysql> select * from faculty order by faculty_id;

Page6 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
+------------+-----------------+--------+------+------+--------+-------------+
| Faculty_id | Name | Gender | Age | Dept | Salary | Year_joined |
+------------+-----------------+--------+------+------+--------+-------------+
| 101 | Rohit Mahajan | M | 30 | ME | 344.90 | 2008 |
| 102 | Karan Singh | F | 34 | IT | 783.00 | 2012 |
| 105 | Rahul Sharma | M | 51 | ME | 693.00 | 2010 |
| 106 | Monika Jain | F | 25 | CSE | 548.00 | 2009 |
| 107 | Sheetal Agrawal | F | 40 | AU | 678.00 | 2009 |
| 110 | Jitendra Sharma | M | 45 | CE | 993.00 | 2005 |
+------------+-----------------+--------+------+------+--------+-------------+
6 rows in set (0.00 sec)

mysql> select * from student order by enrollment_num; +----------------

+-----------------+--------+------+--------+
| enrollment_num | name | gender | age | branch |
+----------------+-----------------+--------+------+--------+
| 34 | Garvit Paliwal | M | 19 | CSE |
| 39 | Gaurav Sharma | M | 18 | CSBS |
| 44 | Aayushi Talreja | F | 18 | EC |
| 47 | Kratik Mathur | M | 18 | ME |
| 59 | Diksha Rathore | F | 19 | AU |
| 67 | Mohit Pherwani | M | 18 | IT |
| 140 | Ram Sharma | M | 19 | CSE |
| 150 | Devansh Singh | M | 18 | ME |
| 155 | Shruti Malviya | F | 18 | CE |
| 159 | Dipika Jain | F | 18 | IT |
| 170 | Sakshi Sharma | F | 19 | AU |
+----------------+-----------------+--------+------+--------+
11 rows in set (0.00 sec)

mysql> select max(salary) from faculty;

+-------------+
| max(salary) |
+-------------+
| 993.00 |
+-------------+
1 row in set (0.00 sec)
mysql> select min(age) from student;
+----------+
| min(age) |
+----------+
| 18 |
+----------+ 1 row in
set (0.00 sec)

mysql> select avg(age) from student;

+----------+
| avg(age) |
+----------+

Page7 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
| 18.3636 |
+----------+ 1 row in
set (0.00 sec)

mysql> select sum(salary) from faculty;

+-------------+
| sum(salary) |
+-------------+
| 4039.90 |
+-------------+ 1 row
in set (0.00 sec)

mysql> alter table faculty add dob date after Age;

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

mysql> alter table faculty modify Faculty_id varchar(10);

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

mysql> alter table faculty change column dob DOB date;

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

mysql> alter table faculty drop column Salary;

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

mysql> alter table faculty add primary key(Faculty_id);

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

mysql> alter table student add primary key(enrollment_num);

Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> delete from student where name = "Anshika Gupta";
Query OK, 0 rows affected (0.00 sec)

mysql> delete from student where enrollment_num = 59;

Query OK, 1 row affected (0.00 sec)

mysql> create table cs_dept(id int primary key, fname char(20) unique key, lname
char(20), age smallint, dob date not null); Query OK, 0 rows affected (0.01 sec)

mysql> insert into cs_dept values(201, "Londyn", "Benson", 22,"2000-09-07");

Query OK, 1 row affected (0.00 sec)

mysql> insert into cs_dept values(202, "Justice", "Boyd", 23,"1999-07-23");

Query OK, 1 row affected (0.01 sec)

Page8 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
mysql> insert into cs_dept values(203, "Maddison", "Atkins", 22,"2000-09-16");
Query OK, 1 row affected (0.00 sec)

mysql> insert into cs_dept values(204, "Kobe", "Olsen", 24,"1998-07-

15"); ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into cs_dept values(205, "Angela", "Juarez", 20,"2001-05-08 ");

Query OK, 1 row affected (0.00 sec)

mysql> insert into cs_dept values(206, "Angel", "Lee", 20,"2001-08-03");

Query OK, 1 row affected (0.01 sec)

mysql> insert into cs_dept values(207, "Braeden", "Wiley", 22,"1999-05-27");

Query OK, 1 row affected (0.00 sec)

mysql> insert into cs_dept values(208, "Killian", "Calhoun", 20,"2001-03-02");

Query OK, 1 row affected (0.00 sec)

mysql> insert into cs_dept values(209, "Cheyanne", "Fisher", 20,"2001-03-02");

Query OK, 1 row affected (0.00 sec)

mysql> insert into cs_dept values(210, "Bridget", "Horton", 21,"2000-08-29");

Query OK, 1 row affected (0.00 sec)

mysql> insert into cs_dept values(211, "Leyla", "Webster", 22,"1999-05-16");

Query OK, 1 row affected (0.00 sec)

Page9 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems

Lab 4
Theory
The REGEXP_LIKE() function in MySQL is used for pattern matching. It compares whether the given
strings match a regular expression or not. It returns 1 if the strings match the regular expression and
return 0 if no match is found.
+----------------------------------------------------------------------------+

CODE:

mysql> use college;

Database changed
mysql> alter table student drop primary key;
Query OK, 10 rows affected (0.05 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> select * from student;

+----------------+-----------------+--------+------+--------+
| enrollment_num | name | gender | age | branch |
+----------------+-----------------+--------+------+--------+
| 34 | Garvit Paliwal | M | 19 | CSE |
| 39 | Gaurav Sharma | M | 18 | CSBS |
| 44 | Aayushi Talreja | F | 18 | EC |
| 47 | Kratik Mathur | M | 18 | ME |
| 67 | Mohit Pherwani | M | 18 | IT |
| 140 | Ram Sharma | M | 19 | CSE |
| 150 | Devansh Singh | M | 18 | ME |
| 155 | Shruti Malviya | F | 18 | CE |
| 159 | Dipika Jain | F | 18 | IT |
| 170 | Sakshi Sharma | F | 19 | AU |
+----------------+-----------------+--------+------+--------+
10 rows in set (0.00 sec)

mysql> alter table student add primary key(name, branch);

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

mysql> desc student;

+----------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+----------+------+-----+---------+-------+
| enrollment_num | int | NO | | NULL | |
| name | char(20) | NO | PRI | NULL | |
| gender | char(1) | YES | | NULL | |
| age | int | YES | | NULL | |
| branch | char(10) | NO | PRI | NULL | |
+----------------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

Page10 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
mysql> drop table cs_dept; Query OK,
0 rows affected (0.01 sec)

mysql> drop table cs_dept_table;

ERROR 1051 (42S02): Unknown table 'college.cs_dept_table'
mysql> select enrollment_num as studentID from student;
+-----------+
| studentID |
+-----------+
| 44 |
| 150 |
| 159 |
| 34 |
| 39 |
| 47 |
| 67 |
| 140 |
| 170 |
| 155 |
+-----------+ 10 rows in
set (0.00 sec)

mysql> select faculty_id from faculty as Teachers;

+------------+
| faculty_id |
+------------+
| 101 |
| 102 |
| 105 |
| 106 |
| 107 |
| 110 |
+------------+ 6 rows in
set (0.00 sec)

mysql> select Name from faculty as Teachers;

Page11 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
| studentID | Full_Name |
+-----------+-----------------+
| 44 | Aayushi Talreja |
| 150 | Devansh Singh |
| 159 | Dipika Jain |
| 34 | Garvit Paliwal |
| 39 | Gaurav Sharma |
| 47 | Kratik Mathur |
| 67 | Mohit Pherwani |
| 140 | Ram Sharma |
| 170 | Sakshi Sharma |
| 155 | Shruti Malviya |
+-----------+-----------------+
10 rows in set (0.00 sec)

mysql> select * from student;

+----------------+-----------------+--------+------+--------+
| enrollment_num | name | gender | age | branch |
+----------------+-----------------+--------+------+--------+
| 44 | Aayushi Talreja | F | 18 | EC |
| 150 | Devansh Singh | M | 18 | ME |
| 159 | Dipika Jain | F | 18 | IT |
| 34 | Garvit Paliwal | M | 19 | CSE |
| 39 | Gaurav Sharma | M | 18 | CSBS |
| 47 | Kratik Mathur | M | 18 | ME |
| 67 | Mohit Pherwani | M | 18 | IT |
| 140 | Ram Sharma | M | 19 | CSE |
| 170 | Sakshi Sharma | F | 19 | AU |
| 155 | Shruti Malviya | F | 18 | CE |
+----------------+-----------------+--------+------+--------+
10 rows in set (0.00 sec)

mysql> select enrollment_num, concat(name, " , ", branch) as Student_Name from

student;
+----------------+----------------------+
| enrollment_num | Student_Name |
+----------------+----------------------+
| 44 | Aayushi Talreja , EC |
| 150 | Devansh Singh , ME |
| 159 | Dipika Jain , IT |
| 34 | Garvit Paliwal , CSE |
| 39 | Gaurav Sharma , CSBS |
| 47 | Kratik Mathur , ME |
| 67 | Mohit Pherwani , IT |
| 140 | Ram Sharma , CSE |
| 170 | Sakshi Sharma , AU |
| 155 | Shruti Malviya , CE |
+----------------+----------------------+
10 rows in set (0.00 sec)

Page12 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
mysql> select * from faculty limit 3;
+------------+---------------+--------+------+------+------+-------------+
| Faculty_id | Name | Gender | Age | DOB | Dept | Year_joined |
+------------+---------------+--------+------+------+------+-------------+
| 101 | Rohit Mahajan | M | 30 | NULL | ME | 2008 |
| 102 | Karan Singh | F | 34 | NULL | IT | 2012 |
| 105 | Rahul Sharma | M | 51 | NULL | ME | 2010 |
+------------+---------------+--------+------+------+------+-------------+
3 rows in set (0.00 sec)

mysql> select * from faculty where regexp_like(name, '[a-g]'); +------------

+-----------------+--------+------+------+------+-------------+
| Faculty_id | Name | Gender | Age | DOB | Dept | Year_joined |
+------------+-----------------+--------+------+------+------+-------------+
| 101 | Rohit Mahajan | M | 30 | NULL | ME | 2008 |
| 102 | Karan Singh | F | 34 | NULL | IT | 2012 |
| 105 | Rahul Sharma | M | 51 | NULL | ME | 2010 |
| 106 | Monika Jain | F | 25 | NULL | CSE | 2009 |
| 107 | Sheetal Agrawal | F | 40 | NULL | AU | 2009 |
| 110 | Jitendra Sharma | M | 45 | NULL | CE | 2005 |
+------------+-----------------+--------+------+------+------+-------------+
6 rows in set (0.01 sec)

mysql> select * from faculty where regexp_like(name, '[y-z]');

Empty set (0.00 sec)

mysql> select * from faculty where regexp_like(name, '[u-z]'); +------------

mysql>

Lab 5
Theory
The INNER JOIN keyword selects records that have matching values in both tables.
The LEFT JOIN keyword returns all records from the left table (table1), and the matching records (if
any) from the right table (table2).
The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records (if
any) from the left table (table1).
The CROSS JOIN keyword returns all records from both tables (table1 and table2).

Page13 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems

+----------------------------------------------------------------------------+

CODE:

mysql> create database company;

Query OK, 1 row affected (0.00 sec)

mysql> use company; Database changed mysql> create table

customers(customerID int primary key, fname char(20) unique key, lname
char(20)); Query OK, 0 rows affected (0.03 sec)

mysql> desc customers;

mysql> insert into customers values(100, "Monika", "Jain");

Query OK, 1 row affected (0.00 sec)

mysql> insert into customers values(101, "Salmon", "Khan");

Query OK, 1 row affected (0.01 sec)

Page14 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
mysql> insert into customers values(102, "Udit", "Sathe");
Query OK, 1 row affected (0.00 sec)

mysql> insert into customers values(103, "Utkarsh", "Chourasia");

Query OK, 1 row affected (0.01 sec)

mysql> insert into customers values(104, "Muskan", "Jain");

Query OK, 1 row affected (0.00 sec)

mysql> insert into customers values(105, "Tejas", "Shah");

Query OK, 1 row affected (0.00 sec)

mysql> insert into customers values(106, "Sneha", "Verma");

Query OK, 1 row affected (0.00 sec)

mysql> insert into customers values(107, "Vanhika", "Juneja");

Query OK, 1 row affected (0.01 sec)

mysql> insert into customers values(108, "Sparsh", "Garg");

Query OK, 1 row affected (0.00 sec)

mysql> insert into customers values(109, "Ujjwal", "Pawar");

Query OK, 1 row affected (0.01 sec)

mysql> insert into customers values(110, "Tanish", "Kohser");

Query OK, 1 row affected (0.00 sec)

mysql> create table orders(customerCODE int primary key, orderID int );

Query OK, 0 rows affected (0.02 sec)

mysql> insert into orders values(100, 1001);

Query OK, 1 row affected (0.00 sec)

mysql> insert into orders values(101, 1002);

Query OK, 1 row affected (0.01 sec)
mysql> insert into orders values(102, 1003);
Query OK, 1 row affected (0.01 sec)

mysql> insert into orders values(103, 1004);

Query OK, 1 row affected (0.00 sec)

mysql> insert into orders values(104, 1005);

Query OK, 1 row affected (0.01 sec)

mysql> mysql> SELECT * FROM customers INNER JOIN

orders ON customers.customerID=orders.customerCODE;
+------------+---------+-----------+--------------+---------+

Page15 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
| customerID | fname | lname | customerCODE | orderID |
+------------+---------+-----------+--------------+---------+
| 100 | Monika | Jain | 100 | 1001 |
| 101 | Salmon | Khan | 101 | 1002 |
| 102 | Udit | Sathe | 102 | 1003 |
| 103 | Utkarsh | Chourasia | 103 | 1004 |
| 104 | Muskan | Jain | 104 | 1005 |
+------------+---------+-----------+--------------+---------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM customers LEFT JOIN orders ON customers.customerID =

mysql> SELECT * FROM customers CROSS JOIN orders; +------------

Page16 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
| 100 | Monika | Jain | 104 | 1005 |
| 100 | Monika | Jain | 103 | 1004 |
| 100 | Monika | Jain | 102 | 1003 |
| 100 | Monika | Jain | 101 | 1002 |
| 100 | Monika | Jain | 100 | 1001 |
| 101 | Salmon | Khan | 104 | 1005 |
| 101 | Salmon | Khan | 103 | 1004 |
| 101 | Salmon | Khan | 102 | 1003 |
| 101 | Salmon | Khan | 101 | 1002 |
| 101 | Salmon | Khan | 100 | 1001 |
| 102 | Udit | Sathe | 104 | 1005 |
| 102 | Udit | Sathe | 103 | 1004 |
| 102 | Udit | Sathe | 102 | 1003 |
| 102 | Udit | Sathe | 101 | 1002 |
| 102 | Udit | Sathe | 100 | 1001 |
| 103 | Utkarsh | Chourasia | 104 | 1005 |
| 103 | Utkarsh | Chourasia | 103 | 1004 |
| 103 | Utkarsh | Chourasia | 102 | 1003 |
| 103 | Utkarsh | Chourasia | 101 | 1002 |
| 103 | Utkarsh | Chourasia | 100 | 1001 |
| 104 | Muskan | Jain | 104 | 1005 |
| 104 | Muskan | Jain | 103 | 1004 |
| 104 | Muskan | Jain | 102 | 1003 |
| 104 | Muskan | Jain | 101 | 1002 |
| 104 | Muskan | Jain | 100 | 1001 |
| 105 | Tejas | Shah | 104 | 1005 |
| 105 | Tejas | Shah | 103 | 1004 |
| 105 | Tejas | Shah | 102 | 1003 |
| 105 | Tejas | Shah | 101 | 1002 |
| 105 | Tejas | Shah | 100 | 1001 |
| 106 | Sneha | Verma | 104 | 1005 |
| 106 | Sneha | Verma | 103 | 1004 |
| 106 | Sneha | Verma | 102 | 1003 |
| 106 | Sneha | Verma | 101 | 1002 |
| 106 | Sneha | Verma | 100 | 1001 |
| 107 | Vanhika | Juneja | 104 | 1005 |
| 107 | Vanhika | Juneja | 103 | 1004 |
| 107 | Vanhika | Juneja | 102 | 1003 |
| 107 | Vanhika | Juneja | 101 | 1002 |
| 107 | Vanhika | Juneja | 100 | 1001 |
| 108 | Sparsh | Garg | 104 | 1005 |
| 108 | Sparsh | Garg | 103 | 1004 |
| 108 | Sparsh | Garg | 102 | 1003 |
| 108 | Sparsh | Garg | 101 | 1002 |
| 108 | Sparsh | Garg | 100 | 1001 |

Page17 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
| 109 | Ujjwal | Pawar | 104 | 1005 |
| 109 | Ujjwal | Pawar | 103 | 1004 |
| 109 | Ujjwal | Pawar | 102 | 1003 |
| 109 | Ujjwal | Pawar | 101 | 1002 |
| 109 | Ujjwal | Pawar | 100 | 1001 |
| 110 | Tanish | Kohser | 104 | 1005 |
| 110 | Tanish | Kohser | 103 | 1004 |
| 110 | Tanish | Kohser | 102 | 1003 |
| 110 | Tanish | Kohser | 101 | 1002 |
| 110 | Tanish | Kohser | 100 | 1001 |
+------------+---------+-----------+--------------+---------+
55 rows in set (0.00 sec)

mysql>

Lab 6
Theory
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or
more real tables in the database.
You can add SQL statements and functions to a view and present the data as if the data were coming
from one single table.
A view is created with the CREATE VIEW statement.
+----------------------------------------------------------------------------+

CODE:

mysql> create database college;

Query OK, 1 row affected (0.01 sec)

mysql> use college; Database changed mysql> create table student(enrollment_num

int, name char(20), gender char(1), age int, branch char(10));
Query OK, 0 rows affected (0.05 sec)

mysql> desc student;

Page18 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
mysql> insert into student values(34, "Garvit", "M", 18, "CSE");
Query OK, 1 row affected (0.03 sec)

mysql> insert into student values(44, "Aayushi", "F", 17, "EC");

Query OK, 1 row affected (0.04 sec)

mysql> insert into student values(47, "Kratik", "M", 19, "ME");

Query OK, 1 row affected (0.04 sec)

mysql> insert into student values(59, "Diksha", "F", 18, "AU");

Query OK, 1 row affected (0.04 sec)

mysql> insert into student values(39, "Gaurav", "M", 18, "CSBS");

Query OK, 1 row affected (0.03 sec)
mysql> insert into student values(67, "Mohit", "M", 19, "IT");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(140, "Ram Sharma", "M", 18, "CSE");
Query OK, 1 row affected (0.04 sec)

mysql> insert into student values(150, "Devansh Singh", "M", 19, "ME");
Query OK, 1 row affected (0.04 sec)

mysql> insert into student values(155, "Shruti Malviya", "F", 19, "CE");
Query OK, 1 row affected (0.04 sec)

mysql> insert into student values(159, "Dipika Jain", "F", 19, "IT");
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(170, "Sakshi Sharma", "F", 18, "AU");
Query OK, 1 row affected (0.01 sec)

mysql> update student set gender = 'F' where name = 'Anshika Gupta';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> update student set age=19 where enrollment_num in (34, 140, 59, 170);
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0

mysql> update student set age=18 where enrollment_num not in (34, 140, 59, 170);
Query OK, 6 rows affected (0.01 sec)
Rows matched: 7 Changed: 6 Warnings: 0

mysql> update student set name = "Diksha Rathore" where name = "Diksha";
Query OK, 1 row affected (0.01 sec) Rows
matched: 1 Changed: 1 Warnings: 0

mysql> update student set name = "Neha Reddy" where name = "Neha";

Page19 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> update student set name = "Gaurav Sharma" where name = "Gaurav";
Query OK, 1 row affected (0.00 sec) Rows
matched: 1 Changed: 1 Warnings: 0

mysql> update student set name = "Garvit Paliwal" where name = "Garvit";
Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1

Warnings: 0 mysql> update student set name = "Kratik Mathur" where name

= "Kratik"; Query OK, 1 row affected (0.00 sec) Rows matched: 1

Changed: 1 Warnings: 0

mysql> update student set name = "Aayushi Talreja" where name = "Aayushi";
Query OK, 1 row affected (0.00 sec) Rows
matched: 1 Changed: 1 Warnings: 0

mysql> update student set name = "Mohit Pherwani" where name = "Mohit";
Query OK, 1 row affected (0.01 sec) Rows
matched: 1 Changed: 1 Warnings: 0

mysql> CREATE VIEW twotable AS SELECT student.name, student.branch FROM student;

Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM twotable; +-----------------+--------

+
| name | branch |
+-----------------+--------+
| Garvit Paliwal | CSE |
| Aayushi Talreja | EC |
| Kratik Mathur | ME |
| Diksha Rathore | AU |
| Gaurav Sharma | CSBS |
| Mohit Pherwani | IT |
| Ram Sharma | CSE |
| Devansh Singh | ME |
| Shruti Malviya | CE |
| Dipika Jain | IT |
| Sakshi Sharma | AU |
+-----------------+--------+
11 rows in set (0.00 sec)
Lab 7
Theory
The GROUP BY statement groups rows that have the same values into summary rows.
The GROUP BY statement is often used with aggregate functions {COUNT(), MAX(), MIN(), SUM(),
AVG()} to group the result-set by one or more columns.

Page20 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in
descending order, use the DESC keyword.
The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate
functions.
The UNION operator is used to combine the result-set of two or more SELECT statements.
Every SELECT statement within UNION must have the same number of columns
The columns must also have similar data types
The columns in every SELECT statement must also be in the same order
+----------------------------------------------------------------------------+

CODE:

mysql> create database college;

Query OK, 1 row affected (0.01 sec)

mysql> use college; to student values(170, "Sakshi Sharma", "F", 18,

"AU");Database changed mysql> create table student(enrollment_num int,
full_name char(20), gender char(1), age int, branch char(10)); Query OK, 0
rows affected (0.02 sec)

mysql> desc student;

mysql> insert into student values(34, "Garvit", "M", 18, "CSE");

Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(44, "Aayushi", "F", 17, "EC");

Query OK, 1 row affected (0.00 sec) mysql> insert into student

values(47, "Kratik", "M", 19, "ME"); Query OK, 1 row affected

(0.00 sec)

mysql> insert into student values(59, "Diksha", "F", 18, "AU");

Query OK, 1 row affected (0.01 sec)

Page21 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
mysql> insert into student values(39, "Gaurav", "M", 18, "CSBS");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(67, "Mohit", "M", 19, "IT");

Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(155, "Shruti Malviya", "F", 19, "CE");
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(159, "Dipika Jain", "F", 19, "IT");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(170, "Sakshi Sharma", "F", 18, "AU");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(140, "Ram Sharma", "M", 18, "CSE");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(150, "Devansh Singh", "M", 19, "ME");
Query OK, 1 row affected (0.01 sec)

mysql> SELECT COUNT(enrollment_num), full_name FROM student GROUP BY full_name;

+-----------------------+----------------+
| COUNT(enrollment_num) | full_name |
+-----------------------+----------------+
| 1 | Garvit |
| 1 | Aayushi |
| 1 | Kratik |
| 1 | Diksha |
| 1 | Gaurav |
| 1 | Mohit |
| 2 | Ram Sharma |
| 1 | Shruti Malviya |
| 1 | Dipika Jain |
| 1 | Sakshi Sharma |
| 1 | Devansh Singh |
+-----------------------+----------------+
11 rows in set (0.00 sec)

mysql> SELECT COUNT(enrollment_num), full_name FROM student GROUP BY full_name

ORDER BY COUNT(branch) DESC;
+-----------------------+----------------+
| COUNT(enrollment_num) | full_name | +-----------------------
+----------------+
| 2 | Ram Sharma |
| 1 | Garvit |
| 1 | Aayushi |
| 1 | Kratik |
| 1 | Diksha |

Page22 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
| 1 | Gaurav |
| 1 | Mohit |
| 1 | Shruti Malviya |
| 1 | Dipika Jain |
| 1 | Sakshi Sharma |
| 1 | Devansh Singh |
+-----------------------+----------------+
11 rows in set (0.00 sec)

mysql> SELECT * FROM student ORDER BY branch ; +----------------+-----------

+--------+------+--------+
| enrollment_num | full_name | gender | age | branch |
+----------------+-----------+--------+------+--------+
| 59 | Diksha | F | 18 | AU |
| 170 | Sakshi | F | 18 | AU |
| 155 | Shruti | F | 19 | CE |
| 39 | Gaurav | M | 18 | CSBS |
| 34 | Garvit | M | 18 | CSE |
| 140 | Ram | M | 18 | CSE |
| 44 | Aayushi | F | 17 | EC |
| 67 | Mohit | M | 19 | IT |
| 159 | Dipika | F | 19 | IT |
| 47 | Kratik | M | 19 | ME |
| 150 | Devansh | M | 19 | ME |
+----------------+-----------+--------+------+--------+
11 rows in set (0.00 sec)

mysql> SELECT * FROM student;

+----------------+-----------+--------+------+--------+
| enrollment_num | full_name | gender | age | branch |
+----------------+-----------+--------+------+--------+
| 34 | Garvit | M | 18 | CSE |
| 44 | Aayushi | F | 17 | EC |
| 47 | Kratik | M | 19 | ME |
| 59 | Diksha | F | 18 | AU |
| 39 | Gaurav | M | 18 | CSBS |
| 67 | Mohit | M | 19 | IT |
| 140 | Ram | M | 18 | CSE |
| 150 | Devansh | M | 19 | ME |
| 155 | Shruti | F | 19 | CE |
| 159 | Dipika | F | 19 | IT |
| 170 | Sakshi | F | 18 | AU |
+----------------+-----------+--------+------+--------+ 11 rows in set (0.01 sec)

mysql> SELECT * FROM student where gender="F" Having age=18; +----------------

+-----------+--------+------+--------+
| enrollment_num | full_name | gender | age | branch |
+----------------+-----------+--------+------+--------+
| 59 | Diksha | F | 18 | AU |

Page23 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
| 170 | Sakshi | F | 18 | AU |
+----------------+-----------+--------+------+--------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM student where gender="F" Having age=18 Union SELECT * FROM
student where gender="M" Having age=18; +----------------+-----------+--------
+------+--------+
| enrollment_num | full_name | gender | age | branch |
+----------------+-----------+--------+------+--------+
| 59 | Diksha | F | 18 | AU |
| 170 | Sakshi | F | 18 | AU |
| 34 | Garvit | M | 18 | CSE |
| 39 | Gaurav | M | 18 | CSBS |
| 140 | Ram | M | 18 | CSE |
+----------------+-----------+--------+------+--------+
5 rows in set (0.00 sec)

Page24 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems

Lab 8
Theory
The EXISTS operator is used to test for the existence of any record in a subquery.
The EXISTS operator returns TRUE if the subquery returns one or more records.
The ANY operator:
returns a boolean value as a result
returns TRUE if ANY of the subquery values meet the condition
The ALL operator:
returns a boolean value as a result returns TRUE if ALL
of the subquery values meet the condition is used with SELECT,
WHERE and HAVING statements
+----------------------------------------------------------------------------+

CODE:

mysql> create database college;

Query OK, 1 row affected (0.00 sec)

mysql> use college; Database changed mysql> create table

student(enrollment_num int, full_name char(20), gender char(1), age int,
branch char(10)); Query OK, 0 rows affected (0.01 sec)

mysql> desc student;

mysql> insert into student values(34, "Garvit", "M", 18, "CSE");

Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(44, "Aayushi", "F", 17, "EC");

Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(47, "Kratik", "M", 19, "ME");

Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(59, "Diksha", "F", 18, "AU");
Query OK, 1 row affected (0.01 sec)

Page25 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
mysql> insert into student values(39, "Gaurav", "M", 18, "CSBS");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(67, "Mohit", "M", 19, "IT");

Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(140, "Ram", "M", 18, "CSE");

Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(150, "Devansh", "M", 19, "ME");

Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(155, "Shruti", "F", 19, "CE");

Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(156, "Shruti", "F", 19, "CSE");

Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(159, "Dipika", "F", 19, "IT");

Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(170, "Saksh", "F", 18, "AU");

Query OK, 1 row affected (0.01 sec)

mysql> create table faculty(Faculty_id mediumint, Name char(30), Gender

char(1), Age int, branch char(10), Salary float(5, 2), Year_joined year);
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> desc faculty;

mysql> insert into faculty values(106, "Monika Jain", "F", 25, "CSE",

Page26 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
548.0,'2009');
Query OK, 1 row affected (0.00 sec)

mysql> insert into faculty values(102, "Kiran Talwariya", "F", 34, "IT",
783.0,'2012');
Query OK, 1 row affected (0.00 sec)

mysql> insert into faculty values(110, "Jitendra Sharma", "M", 45, "CE",
993.0,'2005');
Query OK, 1 row affected (0.00 sec)

mysql> insert into faculty values(107, "Sheetal Agrawal", "F", 40, "AU",
678.0,'2009');
Query OK, 1 row affected (0.00 sec)

mysql> insert into faculty values(105, "Rahul Sharma", "M", 51, "ME",
693.0,'2010');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT full_name FROM student WHERE branch=ALL (SELECT branch FROM
faculty WHERE Gender="F" );
Empty set (0.00 sec)

Page28 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems

Lab 9
Theory
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY
in another table.
ON DELETE CASCADE clause in MySQL is used to automatically remove the matching records from the
child table when we delete the rows from the parent table. It is a kind of referential action related to
the foreign key.
A foreign key with "set null on delete" means that if a record in the parent table is deleted, then the
corresponding records in the child table will have the foreign key fields set to NULL. The records in the
child table will not be deleted in SQL Server.
+----------------------------------------------------------------------------+

CODE:

mysql> create database college;

Query OK, 1 row affected (0.01 sec)

mysql> use college; Database changed mysql> create table student(enrollment_num

int primary key, full_name char(20), gender char(1), age int, branch char(10));
Query OK, 0 rows affected (0.01 sec)

mysql> desc student;

mysql> insert into student values(101, "Garvit", "M", 18, "CSE");

Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(106, "Shruti", "F", 19, "CSE");

Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(110, "Ram", "M", 18, "CSE");

Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(102, "Aayushi", "F", 17, "EC");

Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(107, "Shruti", "F", 19, "EC");
Query OK, 1 row affected (0.00 sec)

Page29 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
mysql> insert into student values(105, "Devansh", "M", 19, "ME");
Query OK, 1 row affected (0.01 sec)

mysql> SET FOREIGN_KEY_CHECKS=0;

Query OK, 0 rows affected (0.00 sec)

mysql> create table faculty(Faculty_id int primary key, Name char(30), Gender
char(1), Age int, branch char(10), Salary float(5, 2), Year_joined year,student_en
int, FOREIGN KEY (student_en) REFERENCES student(enrollment_num) ON DELETE SET
NULL);
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> desc faculty;

mysql> insert into faculty values(101, "Rohit Mahajan", "M", 30, "ME",
344.9,'2008', 101);
Query OK, 1 row affected (0.00 sec)

mysql> insert into faculty values(106, "Monika Jain", "F", 25, "CSE",
548.0,'2009', 106);
Query OK, 1 row affected (0.01 sec)

mysql> insert into faculty values(110, "Jitendra Sharma", "M", 45, "EC",
993.0,'2005', 102);
Query OK, 1 row affected (0.00 sec)

mysql> insert into faculty values(107, "Sheetal Agrawal", "F", 40, "AU",
678.0,'2009', 105);
Query OK, 1 row affected (0.00 sec)
mysql> insert into faculty values(105, "Rahul Sharma", "M", 51, "ME",
693.0,'2010', 105);
Query OK, 1 row affected (0.01 sec)

mysql> desc faculty;

mysql> insert into faculty values(101, "Rohit Mahajan", "M", 30, "ME",
344.9,'2008', 101);
Query OK, 1 row affected (0.00 sec)

mysql> insert into faculty values(106, "Monika Jain", "F", 25, "CSE",
548.0,'2009', 106);
Query OK, 1 row affected (0.01 sec)

mysql> insert into faculty values(110, "Jitendra Sharma", "M", 45, "EC",
993.0,'2005', 102);
Query OK, 1 row affected (0.00 sec)

mysql> insert into faculty values(107, "Sheetal Agrawal", "F", 40, "AU",
678.0,'2009', 105);
Query OK, 1 row affected (0.00 sec)

mysql> insert into faculty values(105, "Rahul Sharma", "M", 51, "ME",
693.0,'2010', 105);
Query OK, 1 row affected (0.01 sec)

mysql> DELETE FROM student WHERE enrollment_num=101;

Query OK, 1 row affected (0.01 sec)

mysql> select * from student;

+----------------+-----------+--------+------+--------+
| enrollment_num | full_name | gender | age | branch |
+----------------+-----------+--------+------+--------+
| 102 | Aayushi | F | 17 | EC |
| 105 | Devansh | M | 19 | ME |
| 106 | Shruti | F | 19 | CSE |
| 107 | Shruti | F | 19 | EC |
| 110 | Ram | M | 18 | CSE |
+----------------+-----------+--------+------+--------+
5 rows in set (0.00 sec)

mysql> select * from faculty;

Page31 CS3CO25
EN20CS301479 Utkarsh Chourasia CS-H Database Management Systems
+------------+-----------------+--------+------+--------+--------+-------------
+-----------+
| Faculty_id | Name | Gender | Age | branch | Salary | Year_joined |
student_en |
+------------+-----------------+--------+------+--------+--------+-------------
+-----------+
| 101 | Rohit Mahajan | M | 30 | ME | 344.90 | 2008 |
101 |
| 105 | Rahul Sharma | M | 51 | ME | 693.00 | 2010 |
105 |
| 106 | Monika Jain | F | 25 | CSE | 548.00 | 2009 |
106 |
| 107 | Sheetal Agrawal | F | 40 | AU | 678.00 | 2009 |
105 |
| 110 | Jitendra Sharma | M | 45 | EC | 993.00 | 2005 |
102 |
+------------+-----------------+--------+------+--------+--------+-------------
+-----------+
5 rows in set (0.00 sec)

Page32 CS3CO25

SQL
No ratings yet
SQL
17 pages
Mysql Queries
100% (1)
Mysql Queries
15 pages
MySQL Basic Commands Tutorial
No ratings yet
MySQL Basic Commands Tutorial
4 pages
Program 11 Create A Binary File With Name and Roll No. Search For A Given Roll Number and Display The Name, If Not Found Display Appropriate Message
No ratings yet
Program 11 Create A Binary File With Name and Roll No. Search For A Given Roll Number and Display The Name, If Not Found Display Appropriate Message
16 pages
MySQL Update, Delete, Alter Commands
No ratings yet
MySQL Update, Delete, Alter Commands
6 pages
MySQL Teacher Table Operations
No ratings yet
MySQL Teacher Table Operations
7 pages
Basic
No ratings yet
Basic
16 pages
SQL Database Operations Guide
No ratings yet
SQL Database Operations Guide
7 pages
Student Database Management
No ratings yet
Student Database Management
12 pages
March 27 (MySQL Commands)
No ratings yet
March 27 (MySQL Commands)
44 pages
Set 1
No ratings yet
Set 1
6 pages
Class 12th Practical File
No ratings yet
Class 12th Practical File
18 pages
20BCS030 - Mateen Khan - Dbms p9
No ratings yet
20BCS030 - Mateen Khan - Dbms p9
12 pages
Table 6
No ratings yet
Table 6
3 pages
SQL Commands for Teens Blog
No ratings yet
SQL Commands for Teens Blog
11 pages
Student-Teacher Database Setup
No ratings yet
Student-Teacher Database Setup
6 pages
Practical File Grade 12 Set 2
No ratings yet
Practical File Grade 12 Set 2
7 pages
Class 12 Computer Science-Updated
No ratings yet
Class 12 Computer Science-Updated
19 pages
Student Record Stack Program
No ratings yet
Student Record Stack Program
4 pages
SQL Student Marks Management
No ratings yet
SQL Student Marks Management
5 pages
SQL 26
No ratings yet
SQL 26
6 pages
Sqltable2223 25 49
No ratings yet
Sqltable2223 25 49
25 pages
Mysql
No ratings yet
Mysql
6 pages
Mysql Commands Notes
No ratings yet
Mysql Commands Notes
34 pages
Mysql Programs
No ratings yet
Mysql Programs
23 pages
MySQL Database and Table Management
No ratings yet
MySQL Database and Table Management
13 pages
Table Creation
No ratings yet
Table Creation
6 pages
SQL Database Practical1..
No ratings yet
SQL Database Practical1..
11 pages
DBMS Lab Record - Old
No ratings yet
DBMS Lab Record - Old
63 pages
My SQL
No ratings yet
My SQL
5 pages
Program File Class12
No ratings yet
Program File Class12
16 pages
Joins
No ratings yet
Joins
9 pages
12th Lab Practical
No ratings yet
12th Lab Practical
20 pages
Exp 3
No ratings yet
Exp 3
2 pages
2024 U Mmu Dit 00187.balinda Onesmas
No ratings yet
2024 U Mmu Dit 00187.balinda Onesmas
6 pages
Mysql - DPS - Lab Record - Xi
No ratings yet
Mysql - DPS - Lab Record - Xi
8 pages
Practical 2
No ratings yet
Practical 2
10 pages
Assignment cs2
No ratings yet
Assignment cs2
16 pages
SQL
No ratings yet
SQL
8 pages
20 5 25
No ratings yet
20 5 25
28 pages
SQL Quiry
No ratings yet
SQL Quiry
18 pages
Mysql
No ratings yet
Mysql
25 pages
Practical Program SQL
No ratings yet
Practical Program SQL
12 pages
Table 1
No ratings yet
Table 1
4 pages
MySQL Data Manipulation Techniques
No ratings yet
MySQL Data Manipulation Techniques
22 pages
Notes 1
No ratings yet
Notes 1
35 pages
Mysql
No ratings yet
Mysql
3 pages
DBMS Lab Record 2017-18 2.0
No ratings yet
DBMS Lab Record 2017-18 2.0
57 pages
Insert Into Products Values
No ratings yet
Insert Into Products Values
11 pages
Dbms Sachin
No ratings yet
Dbms Sachin
19 pages
Day 14 Mysql Commands by Swati Chawla
No ratings yet
Day 14 Mysql Commands by Swati Chawla
59 pages
SQL Codes Revised
No ratings yet
SQL Codes Revised
6 pages
Constarints
No ratings yet
Constarints
8 pages
1.DDL Commands: 1. Creating A Table
No ratings yet
1.DDL Commands: 1. Creating A Table
6 pages
Create Database
No ratings yet
Create Database
9 pages
MySQL Database Management Guide
No ratings yet
MySQL Database Management Guide
25 pages
Class Assignment 1
No ratings yet
Class Assignment 1
2 pages
Pratical4 DBMS
No ratings yet
Pratical4 DBMS
15 pages
Practical No 02 (B)
No ratings yet
Practical No 02 (B)
9 pages
Lagna Lord in Various Houses
100% (1)
Lagna Lord in Various Houses
31 pages
IBlok Waterstop Roofcoat - April 25
No ratings yet
IBlok Waterstop Roofcoat - April 25
2 pages
CAN Protocol for Engineers
No ratings yet
CAN Protocol for Engineers
11 pages
Fundamentals of Industrial Instrumentation and Process Control (By William Dunn)
No ratings yet
Fundamentals of Industrial Instrumentation and Process Control (By William Dunn)
13 pages
Eras of Operations Management - Edited
No ratings yet
Eras of Operations Management - Edited
5 pages
Proposed VCCT Wind Turbine Foundation Bill of Quantities
No ratings yet
Proposed VCCT Wind Turbine Foundation Bill of Quantities
2 pages
Geo English
No ratings yet
Geo English
5 pages
Learning Package CIVE1129 4th Ed
No ratings yet
Learning Package CIVE1129 4th Ed
52 pages
2808-Article Text-11401-1-10-20240713
No ratings yet
2808-Article Text-11401-1-10-20240713
9 pages
Funding Request for PPTAF Operations
No ratings yet
Funding Request for PPTAF Operations
3 pages
Peninsula SD Resolution on SB 5044
No ratings yet
Peninsula SD Resolution on SB 5044
2 pages
Functions of CSO
No ratings yet
Functions of CSO
25 pages
Journal
No ratings yet
Journal
12 pages
Introductory Handbook On Policing Urban Spaces
No ratings yet
Introductory Handbook On Policing Urban Spaces
118 pages
Third Culture Kids (Pollock David C, Van Reken Ruth E) (Z-Library)
No ratings yet
Third Culture Kids (Pollock David C, Van Reken Ruth E) (Z-Library)
370 pages
Plaidoirie en Francais-1.Fr - en
No ratings yet
Plaidoirie en Francais-1.Fr - en
67 pages
Chapter 10
No ratings yet
Chapter 10
9 pages
Physics Guest Faculty Opening
No ratings yet
Physics Guest Faculty Opening
1 page
MBA - SEM IV - Strategic Management - Course Syllabus
No ratings yet
MBA - SEM IV - Strategic Management - Course Syllabus
3 pages
IIM Kozhikode: Final Placements Report 2018
No ratings yet
IIM Kozhikode: Final Placements Report 2018
8 pages
FLR1600
No ratings yet
FLR1600
3 pages
Technical Data Sheet: Century Paper & Board Mills Limited
No ratings yet
Technical Data Sheet: Century Paper & Board Mills Limited
1 page
Hive Database Setup Guide
No ratings yet
Hive Database Setup Guide
2 pages
Half Yearly Exam Syllabus and Time Table Print This
No ratings yet
Half Yearly Exam Syllabus and Time Table Print This
3 pages
Review Questions (CH 1) (2023 W Answers)
No ratings yet
Review Questions (CH 1) (2023 W Answers)
8 pages
An Analysis of Different Methods For Major Energy Saving in Thermal Power Plant
No ratings yet
An Analysis of Different Methods For Major Energy Saving in Thermal Power Plant
9 pages
Workshop Practice Level 2 Assessment Guide
No ratings yet
Workshop Practice Level 2 Assessment Guide
22 pages
Design Qualification Template
29% (7)
Design Qualification Template
4 pages
CURE1401 Lecture 2 Slides
No ratings yet
CURE1401 Lecture 2 Slides
32 pages
EuroKids Parent Brochure PDF
No ratings yet
EuroKids Parent Brochure PDF
6 pages