Smt. Vijayamala V.

Nilajagi Degree College, Hukkeri (4443)

INDEX

SL TITLE Page Date Staff

No. Number Signature

1 Case Study 1: (Select clause, Arithmetic

Operators)
Database: Employee
2 Case Study 2: (group by, having clause)
Database: Bank system
3 Case Study 3: (Logical, Relational
Operators)
Database: Library
4 Case Study 4
Database: Subject

Database Management System Lab B.C.A SEP I SEM Page 1

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

What is SQL?
 SQL (Structured Query language) is a standard programming language used
for managing and manipulating relational databases.
 SQL is used to insert, search, update, and delete database records.
 SQL keywords are NOT case sensitive: select is the same as SELECT

 The MySQL CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a new SQL database.
Syntax
CREATE DATABASE databasename;
Example
CREATE DATABASE svvn;

 The MySQL DROP DATABASE Statement

The DROP DATABASE statement is used to drop an existing SQL database.
Syntax
DROP DATABASE databasename;
Example
DROP DATABASE svvn;

 The MySQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in a database.
Syntax
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
.... );
Example
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

Database Management System Lab B.C.A SEP I SEM Page 2

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

 MySQL ALTER TABLE Statement

 The ALTER TABLE statement is used to add, delete, or modify columns in an
existing table.
 The ALTER TABLE statement is also used to add and drop various constraints on
an existing table.

 ALTER TABLE - ADD Column

To add a column in a table, use the following syntax:
ALTER TABLE table_name
ADD column_name datatype;

 ALTER TABLE - DROP COLUMN

To delete a column in a table, use the following syntax
ALTER TABLE table_name
DROP COLUMN column_name;

 ALTER TABLE - MODIFY COLUMN

To change the data type of a column in a table, use the following syntax:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Primary Key: A primary key is a field (or combinations of fields) that uniquely identifies each
record in a table. It cannot contain NULL values and must have unique entries.

Foreign Key: A foreign key is field (or combinations of fields) in one table that refers to the
primary key in another table, establishing a relationship between the two tables.

Constraints in SQL: Constraints are rules applied to table columns to enforce data integrity.
 Primary Key: Uniquely identifies each record.
 Foreign Key: Establishes relationship between two tables.
 Unique: Ensures all values in a column are distinct.
 Check: Ensures that values in a column satisfy a specific condition.
 Not Null: Ensures a column cannot have NULL values.
 Default: Specifies a default value for a column.

Database Management System Lab B.C.A SEP I SEM Page 3

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

SQL Commands are like instructions to a table. It is used to interact with the database with
some operations. It is also used to perform specific tasks, functions, and queries of data. SQL
can perform various tasks like creating a table, adding data to tables, dropping the table,
modifying the table, set permission for users.

SQL Commands are mainly categorized into five categories:

DDL – Data Definition Language

DQL – Data Query Language

DML – Data Manipulation Language

DCL – Data Control Language

TCL – Transaction Control Language

Database Management System Lab B.C.A SEP I SEM Page 4

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

Case Study 1: (Select clause, Arithmetic Operators)

Database: Employee
1. Create the following tables with suitable constraints and insert tuples.

Table: Employee

Emp_Id First_Name Last_Name Hire_Date Address City

1001 Raghav Patel 11-May-06 83 first street Belagavi
1002 Mohini Rathi 25-Feb-08 842 Vine Ave Athani
1012 Sham Pujari 12-Sep-05 33 Elm St. Mysore
1015 Kriti Dev 19-Dec-06 11 Red Road Belagavi
1016 Sarath Sharma 22-Aug-07 440 MG Road New Delhi
1020 Monika Gupta 07-Jun-08 9 Bandra Mumbai

Table: EmpSalary

Emp_Id Salary Benefits Designation

1001 10000 3000 Manager
1002 8000 1200 Salesman
1012 20000 5000 Director
1015 6500 1300 Clerk
1016 6000 1000 Clerk
1020 8000 1200 Salesman

 First we need to create Date Base “Employee” as follows.

Step 1. Open MySQL Command Line Client in Search bar.
Step 2. Enter the password as given by the instructor.
Step 3. Now type show databases; and click enter button. It will show all the databases present
in your Server.
Step 4. The create database database_name; command will create a new database in Mysql
Server.
mysql> create database Employee;
Query OK, 1 row affected (0.06 sec)

Step 5. Type show databases; to check whether the Employee database is created or not.

Database Management System Lab B.C.A SEP I SEM Page 5

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

 Now we need to create Employee And EmpSalary tables in Employee Database.

Follow below steps.
Step 1. We need to enter into Employee Database using use database_name; command
mysql> use Employee;
Database changed

 Follow the below statements to create Employee and EmpSalary tables.

mysql> CREATE TABLE Employee (

Emp_Id INT PRIMARY KEY,
First_Name VARCHAR(50) NOT NULL,
Last_Name VARCHAR(50) NOT NULL,
Hire_Date DATE NOT NULL,
Address VARCHAR(100),
City VARCHAR(50)
);

mysql> CREATE TABLE EmpSalary (

Emp_Id INT,
Salary DECIMAL(10, 2),
Benefits DECIMAL(10, 2),
Designation VARCHAR(50),
FOREIGN KEY (Emp_Id) REFERENCES Employee(Emp_Id)
);

mysql> INSERT INTO Employee (Emp_Id, First_Name, Last_Name, Hire_Date, Address, City) VALUES
(1001, 'Raghav', 'Patel', '2006-05-11', '83 First Street', 'Belagavi'),
(1002, 'Mohini', 'Rathi','2008-02-25', '842 Vine Ave', 'Athani'),
(1012, 'Sham', 'Pujari', '2005-09-12', '33 Elm St.', 'Mysore'),
(1015, 'Kriti', 'Dev', '2006-12-19', '11 Red Road', 'Belagavi'),
(1016, 'Sarath', 'Sharma', '2007-08-22', '440 MG Road', 'New Delhi'),
(1020, 'Monika', 'Gupta', '2008-06-07', '9 Bandra', 'Mumbai');

mysql> INSERT INTO EmpSalary (Emp_Id, Salary, Benefits, Designation) VALUES

(1001, 10000, 3000, 'Manager'),
(1002, 8000, 1200, 'Salesman'),
(1012, 20000, 5000, 'Director'),
(1015, 6500, 1300, 'Clerk'),
(1016, 6000, 1000, 'Clerk'),
(1020, 8000, 1200, 'Salesman');

Database Management System Lab B.C.A SEP I SEM Page 6

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

Write queries for the following

1. Display FIRSTNAME, LASTNAME, ADDRESS, and CITY of all employees living in PARIS.

mysql> Select first_name, last_name, address, city From employee Where city= 'Paris';
OUTPUT: Empty set (0.00 sec)

2. Display the content of the employee table in descending order of FIRSTNAME.

mysql> SELECT * FROM Employee ORDER BY First_Name DESC;

3. Select the FIRSTNAME and SALARY of the salesman.

mysql> SELECT e.First_Name, [Link] FROM Employee e JOIN EmpSalary es ON e.Emp_Id = es.Emp_Id
WHERE [Link] = 'Salesman';

4. Display the FIRSTNAME, LASTNAME, AND TOTAL SALARY of all employees where TOTAL
SALARY is calculated as SALARY+BENEFITS.
mysql> SELECT e.First_Name, e.Last_Name, ([Link] + [Link]) AS Total_Salary FROM Employee e
JOIN EmpSalary es ON e.Emp_Id = es.Emp_Id;

Database Management System Lab B.C.A SEP I SEM Page 7

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

5. List the Names of employees, who are more than 1 year old in the organization.
mysql> select first_name from employee where timestampdiff ( year, hire_date, curdate()) >=1;

6. Count the number of distinct DESIGNATION from EMPSALARY.

mysql> SELECT COUNT(DISTINCT Designation) AS Distinct_Designations FROM EmpSalary;

7. List the employees whose names have exactly 6 characters.

mysql> SELECT First_Name, Last_Name FROM Employee WHERE char_length(First_Name) = 6 OR
char_length(Last_Name) = 6;

8. Add a new column PHONE_NO to EMPLOYEE and update the records.

mysql> ALTER TABLE Employee ADD Phone_No VARCHAR(15);

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> UPDATE Employee SET Phone_No = '9876543210' WHERE Emp_Id = 1001;

Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Database Management System Lab B.C.A SEP I SEM Page 8

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

mysql> UPDATE Employee SET Phone_No = '9876512345' WHERE Emp_Id = 1002;

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

(Add other phone numbers as required)

9. List employee names, who have joined before 15-Jun-08 and after 16-Jun-07.
mysql> SELECT First_Name, Last_Name FROM Employee WHERE Hire_Date < '2008-06-15' AND
Hire_Date > '2007-06-16';

10. Generate Salary slip with Name, Salary, Benefits, HRA-50%, DA-30%, PF-12%, Calculate gross. Order
the result in descending order of the gross.
mysql> select e.First_Name, e.Last_Name, [Link], [Link], (0.50 * [Link]) AS HRA, (0.30 *
[Link]) AS DA, (0.12 * [Link]) AS PF, ([Link] + [Link] + (0.50 * [Link]) + (0.30 * [Link]) -
(0.12 * [Link])) AS Gross FROM Employee e JOIN EmpSalary es ON e.Emp_Id = es.Emp_Id ORDER BY
Gross DESC;

Database Management System Lab B.C.A SEP I SEM Page 9

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

Case Study 2: (group by, having clause) Database: Bank system

Create the following table and insert tuples with suitable constraints
Table: Account

Account_No Cust_Name Brach_ID

AE0012856 Reena SB002
AE1185698 Akhil SB001
AE1203996 Daniel SB004
AE1225889 Roy SB002
AE8532166 Sowparnika SB003
AE8552266 Anil SB003
AE1003996 Saathwik SB004
AE1100996 Swarna SB002

Table: Branch
Branch_ID Branch_Name Branch_City
SB001 Malleshwaram Bangalore
SB002 MG Road Bangalore
SB003 MG Road Mysore
SB004 Jainagar Mysore

Table: Depositor
Account_No Branch_Id Balance
AE0012856 SB002 12000
AE1203996 SB004 58900
AE8532166 SB003 40000
AE1225889 SB002 150000

Table: Loan
Account_No Branch_Id Balance
AE1185698 SB001 102000
AE8552266 SB003 40000
AE1003996 SB004 15000
AE1100996 SB002 100000

mysql> create database Bank_system;

mysql> use Bank_system;

Database Management System Lab B.C.A SEP I SEM Page 10

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

 Create the Branch table

mysql> CREATE TABLE Branch (
Branch_ID VARCHAR(10) PRIMARY KEY,
Branch_Name VARCHAR(50) NOT NULL,
Branch_City VARCHAR(50) NOT NULL
);

 Create the Account table

mysql> CREATE TABLE Account (
Account_No VARCHAR(10) PRIMARY KEY,
Cust_Name VARCHAR(50) NOT NULL,
Branch_ID VARCHAR(10),
FOREIGN KEY (Branch_ID) REFERENCES Branch(Branch_ID)
);

 Create the Depositor table

mysql> CREATE TABLE Depositor (
Account_No VARCHAR(10),
Branch_ID VARCHAR(10),
Balance DECIMAL(15, 2) NOT NULL,
PRIMARY KEY (Account_No, Branch_ID),
FOREIGN KEY (Account_No) REFERENCES Account(Account_No),
FOREIGN KEY (Branch_ID) REFERENCES Branch(Branch_ID)
);

 Create the Loan table

mysql> CREATE TABLE Loan (
Account_No VARCHAR(10),
Branch_ID VARCHAR(10),
Balance DECIMAL(15, 2) NOT NULL,
PRIMARY KEY (Account_No, Branch_ID),
FOREIGN KEY (Account_No) REFERENCES Account(Account_No),
FOREIGN KEY (Branch_ID) REFERENCES Branch(Branch_ID)
);

Database Management System Lab B.C.A SEP I SEM Page 11

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

mysql> INSERT INTO Branch VALUES

('SB001', 'Malleshwaram', 'Bangalore'),
('SB002', 'MG Road', 'Bangalore'),
('SB003', 'MG Road', 'Mysore'),
('SB004', 'Jainagar', 'Mysore');

mysql> INSERT INTO Account VALUES

('AE0012856', 'Reena', 'SB002'),
('AE1185698', 'Akhil', 'SB001'),
('AE1203996', 'Daniel', 'SB004'),
('AE1225889', 'Roy', 'SB002'),
('AE8532166', 'Sowparnika', 'SB003'),
('AE8552266', 'Anil', 'SB003'),
('AE1003996', 'Saathwik', 'SB004'),
('AE1100996', 'Swarna', 'SB002');

mysql> INSERT INTO Depositor VALUES

('AE0012856', 'SB002', 12000.00),
('AE1203996', 'SB004', 58900.00),
('AE8532166', 'SB003', 40000.00),
('AE1225889', 'SB002', 150000.00);

mysql> INSERT INTO Loan VALUES

('AE1185698', 'SB001', 102000.00),
('AE8552266', 'SB003', 40000.00),
('AE1003996', 'SB004', 15000.00),
('AE1100996', 'SB002', 100000.00);

Write queries for the following

1. Display the Total Number of accounts present in each branch.
mysql> SELECT a.Branch_ID, b.Branch_Name, COUNT(*) AS Total_Accounts FROM Account a JOIN
Branch b ON a.Branch_ID = b.Branch_ID GROUP BY a.Branch_ID, b.Branch_Name;

Database Management System Lab B.C.A SEP I SEM Page 12

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

2. Display the Total Loan amount in each branch.

mysql> SELECT l.Branch_ID, b.Branch_Name, SUM([Link]) AS Total_Loan_Amount FROM Loan l
JOIN Branch b ON l.Branch_ID = b.Branch_ID GROUP BY l.Branch_ID, b.Branch_Name;

3. Display the Total deposited amount in each branch by descending order

mysql> SELECT d.Branch_ID, b.Branch_Name, SUM([Link]) AS Total_Deposited_Amount FROM
Depositor d JOIN Branch b ON d.Branch_ID = b.Branch_ID GROUP BY d.Branch_ID, b.Branch_Name
ORDER BY Total_Deposited_Amount DESC;

4. Display max , min loan amount present in each city.

mysql> SELECT b.Branch_City, MAX([Link]) AS Max_Loan_Amount, MIN([Link]) AS
Min_Loan_Amount FROM Loan l JOIN Branch b ON l.Branch_ID = b.Branch_ID GROUP BY
b.Branch_City;

5. Display average amount deposited in each branch, each city.

-- Average amount deposited in each branch
mysql> SELECT d.Branch_ID, b.Branch_Name, AVG([Link]) AS Average_Deposited_Amount FROM
Depositor d JOIN Branch b ON d.Branch_ID = b.Branch_ID GROUP BY d.Branch_ID, b.Branch_Name;

Database Management System Lab B.C.A SEP I SEM Page 13

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

-- Average amount deposited in each city

mysql> SELECT b.Branch_City, AVG([Link]) AS Average_Deposited_Amount FROM Depositor d
JOIN Branch b ON d.Branch_ID = b.Branch_ID GROUP BY b.Branch_City;

6. Display maximum of loan amount in each branch where the balance is more than 25000.
mysql> SELECT l.Branch_ID, b.Branch_Name, MAX([Link]) AS Max_Loan_Amount FROM Loan l
JOIN Branch b ON l.Branch_ID = b.Branch_ID WHERE [Link] > 25000 GROUP BY l.Branch_ID,
b.Branch_Name;

7. Display Total Number of accounts present in each city.

mysql> SELECT b.Branch_City, COUNT(a.Account_No) AS Total_Accounts FROM Account a JOIN
Branch b ON a.Branch_ID = b.Branch_ID GROUP BY b.Branch_City;

8. Display all customer details in ascending order of branch_id.

mysql> SELECT a.Account_No, a.Cust_Name, a.Branch_ID, b.Branch_Name FROM Account a JOIN
Branch b ON a.Branch_ID = b.Branch_ID ORDER BY a.Branch_ID ASC;

Database Management System Lab B.C.A SEP I SEM Page 14

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

9. Update Balance to 26000 where account_no=AE1003996.

mysql> UPDATE Loan SET Balance = 26000.00 WHERE Account_No = 'AE1003996';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

10. Display Customer Names with their branch Name.

mysql> SELECT a.Cust_Name, b.Branch_Name FROM Account a JOIN Branch b ON a.Branch_ID =
b.Branch_ID;

Database Management System Lab B.C.A SEP I SEM Page 15

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

Case Study 3: (Logical, Relational Operators)

Database: Library
Create Following tables and insert tuples with suitable constraints

Table: Books
Book_Id Book_Name Author_Name Publishers Price Type Quantity
C0001 The Klone and I Lata Kappor EPP 355 Novel 5
F0001 The Tears William Hopkins First Publ 650 Fiction 20
T0001 My First C++ Brain & Brooke ERP 350 Text 10
T0002 C++ Brainwork‟s [Link] TDH 350 Text 15
F0002 Thunderbolts Ana Roberts First Publ. 750 Fiction 50

Table: Issued
Book_Id Quantity_Issued
T0001 4
C0001 5
F0001 2
T0002 5
F0002 8

mysql> create database Library;

mysql> use Library;

mysql> CREATE TABLE Books (

Book_Id VARCHAR(10) PRIMARY KEY,
Book_Name VARCHAR(100) NOT NULL,
Author_Name VARCHAR(100) NOT NULL,
Publishers VARCHAR(100) NOT NULL,
Price DECIMAL(10, 2) CHECK (Price > 0),
Type VARCHAR(50) CHECK (Type IN ('Novel', 'Fiction', 'Text')),
Quantity INT CHECK (Quantity >= 0)
);

mysql> CREATE TABLE

Issued ( Book_Id
VARCHAR(10),
Quantity_Issued INT CHECK (Quantity_Issued >= 0),
FOREIGN KEY (Book_Id) REFERENCES Books(Book_Id)
);

Database Management System Lab B.C.A SEP I SEM Page 16

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

mysql> INSERT INTO Books VALUES

('C0001', 'The Klone and I', 'Lata Kappor', 'EPP', 355, 'Novel', 5),
('F0001', 'The Tears', 'William Hopkins', 'First Publ.', 650, 'Fiction', 20),
('T0001', 'My First C++', 'Brain & Brooke', 'ERP', 350, 'Text', 10),
('T0002', 'C++ Brainwork”s', '[Link]', 'TDH', 350, 'Text', 15),
('F0002', 'Thunderbolts', 'Ana Roberts', 'First Publ.', 750, 'Fiction', 50);

mysql> INSERT INTO Issued VALUES

('T0001', 4),
('C0001', 5),
('F0001', 2),
('T0002', 5),
('F0002', 8);

Write queries for the following

1. To show Book name, Author name and price of books of First Publ. publisher
mysql> SELECT Book_Name, Author_Name, Price FROM Books WHERE Publishers = 'First Publ.';

2. Display Book id, Book name and publisher of books having quantity more than 8 and price less than 500
mysql> SELECT Book_Id, Book_Name, Publishers FROM Books WHERE Quantity > 8 AND Price <
500;

3. Select Book id, book name, author name of books which is published by other than ERP publishers and price
between 300 to 700
mysql> SELECT Book_Id, Book_Name, Author_Name FROM Books WHERE Publishers <> 'ERP' AND
Price BETWEEN 300 AND 700;

Database Management System Lab B.C.A SEP I SEM Page 17

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

4. Generate a Bill with Book_id, Book_name, Publisher, Price, Quantity, 4% of VAT “Total”
mysql> SELECT Book_Id, Book_Name, Publishers, Price, Quantity, (Price * Quantity * 1.04) AS Total
FROM Books;

5. Display book details with book id‟s C0001, F0001, T0002, F0002 (Hint: use IN operator)
mysql> SELECT * FROM Books WHERE Book_Id IN ('C0001', 'F0001', 'T0002', 'F0002');

6. Display Book list other than, type Novel and Fiction

mysql> SELECT * FROM Books WHERE Type NOT IN ('Novel', 'Fiction');

Database Management System Lab B.C.A SEP I SEM Page 18

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

7. Display book details with the author name starting with letter “A”
mysql> SELECT * FROM Books WHERE Author_Name LIKE 'A%';

8. Display book details with the author name starting with letter “T” and ending with “S”
mysql> SELECT * FROM Books WHERE Author_Name LIKE 'T%S';

Empty set (0.00 sec)

9. Select Book_Id, Book_Name, Author Name, Quantity Issued where Books.Books_Id = Issued.Book_Id
mysql> SELECT B.Book_Id, B.Book_Name, B.Author_Name, I.Quantity_Issued FROM Books B
JOIN Issued I ON B.Book_Id = I.Book_Id;

[Link] the book_name, Author_name, Price. In ascending order of Book_name and then in descending order of
price
mysql> SELECT Book_Name, Author_Name, Price FROM Books ORDER BY Book_Name ASC, Price
DESC;

Database Management System Lab B.C.A SEP I SEM Page 19

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

Case Study 4
Database: Subject
Create Following table and insert tuples with suitable constraints
Table – Physics
Regno Name Year Combination
AJ00325 Ashwin First PCM
AJ00225 Swaroop Second PMCs
AJ00385 Sarika Third PME
AJ00388 Rupa First PMCs

Table – Computer Science

Regno Name Year Combination
AJ00225 Swaroop Second PMCs
AJ00296 Tejas Second BCA
AJ00112 Geeta First BCA
AJ00388 Rupa First PMCs

mysql> create database Subject;

mysql> use subject;

mysql> CREATE TABLE Physics (

Regno VARCHAR(10) PRIMARY KEY, Name
VARCHAR(50) NOT NULL, Year
VARCHAR(10) NOT NULL,
Combination VARCHAR(10) NOT NULL);

mysql> CREATE TABLE ComputerScience ( Regno

VARCHAR(10) PRIMARY KEY, Name
VARCHAR(50) NOT NULL, Year
VARCHAR(10) NOT NULL,
Combination VARCHAR(10) NOT NULL);

mysql> INSERT INTO Physics VALUES ('AJ00325',

'Ashwin', 'First', 'PCM'),
('AJ00225', 'Swaroop', 'Second', 'PMCs'),
('AJ00385', 'Sarika', 'Third', 'PME'),
('AJ00388', 'Rupa', 'First', 'PMCs');

Database Management System Lab B.C.A SEP I SEM 20

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

mysql> INSERT INTO ComputerScience VALUES ('AJ00225',

'Swaroop', 'Second', 'PMCs'),
('AJ00296', 'Tejas', 'Second', 'BCA'),
('AJ00112', 'Geeta', 'First', 'BCA'),
('AJ00388', 'Rupa', 'First', 'PMCs');

Write queries for the following

1. Select all students from Physics and Computer Science
mysql> SELECT * FROM Physics UNION SELECT * FROM ComputerScience;

2. Select students common in Physics and Computer Science

mysql> SELECT * FROM Physics WHERE Regno IN (SELECT Regno FROM ComputerScience);

3. Display all student details those are studying in the second year
mysql> SELECT * FROM Physics WHERE Year = 'Second' UNION SELECT * FROM ComputerScience
WHERE Year = 'Second';

Database Management System Lab B.C.A SEP I SEM 21

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

4. Display students who are studying both Physics and computer science in the second year
mysql> SELECT * FROM Physics WHERE Year = 'Second' AND Regno IN (SELECT Regno FROM
ComputerScience WHERE Year = 'Second');

5. Display the students studying only physics

mysql> SELECT * FROM Physics WHERE Regno NOT IN (SELECT Regno FROM ComputerScience);

6. Display the students studying only Computer Science

mysql> SELECT * FROM ComputerScience WHERE Regno NOT IN (SELECT Regno FROM Physics);

7. Select all students having PMCs combination

mysql> SELECT * FROM Physics WHERE Combination = 'PMCs' UNION SELECT * FROM
ComputerScience WHERE Combination = 'PMCs';

Database Management System Lab B.C.A SEP I SEM 22

 Smt. Vijayamala V. Nilajagi Degree College, Hukkeri (4443)

8. Select all students having a BCA combination

mysql> SELECT * FROM ComputerScience WHERE Combination = 'BCA';

9. Select all students studying in the Third year

mysql> SELECT * FROM Physics WHERE Year = 'Third' UNION SELECT * FROM ComputerScience
WHERE Year = 'Third';

10. Rename table Computer Science to CS

mysql> ALTER TABLE ComputerScience RENAME TO CS;
Query OK, 0 rows affected (0.06 sec)

Database Management System Lab B.C.A SEP I SEM 23