PART B

Create the following tables with properly specifying Primary keys, foreign keys and solve the
following queries:

BRANCH (Branchid, Branchname, HOD)

CREATE TABLE BRANCH (Branchid integer primary key, Branchname varchar2 (15) not null,
HOD varchar2 (10));

DESC BRANCH;

+------------------------+-----------------+-------+--------+-----------+--------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------+-------+--------+-----------+--------+
| BRANCHID | int (5) | NO | PRI | NULL | |
| BRANCHNAME | varchar (15) | NO | | NULL | |
| HOD | varchar (10) | YES | | NULL | |
+------------------------+------------------+------+--------+-----------+--------+
3 rows in set (0.03 sec)

INSERT INTO BRANCH (BRANCHID, BRANCHNAME, HOD) VALUES (10,’MCA’,’SUDHA M’);

SELECT * FROM BRANCH;

+----------------+---------------------+----------------+
| BRANCHID | BRANCHNAME | HOD |
+----------------+---------------------+----------------+
| 10 | MCA | SUDHA M |
| 20 | MBA | RENIN |
| 30 | MCOM | KRISHNA |
| 40 | MSC | ADIYA P |
| 50 | MA | RAMESH S |
+----------------+---------------------+----------------+
5 rows in set (0.00 sec)

STUDENT (USN, Name, Address, Branchid, SEM)

CREATE TABLE STUDENT (USN varchar2 (15) primary key, Name varchar2 (15) not null,

Address varchar2 (15) not null, Branchid integer references branch, SEM varchar2 (10));

DESC STUDENT;

+----------------+------------------+------+-------+-----------+---------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-------+-----------+---------+
| USN | varchar (15) | NO | PRI | NULL | |
| NAME | varchar (15) | NO | | NULL | |
| ADDRESS | varchar (15) | NO | | NULL | |
| BRANCHID | int (5) | YES | | NULL | |
| SEM | varchar (10) | YES | | NULL | |
+---------------+------------------+------+-----+-------------+---------+
5 rows in set (0.01 sec)
 EXAMPLE:
INSERT INTO STUDENT VALUES ('SCAS202201','ANURADHA','JAYANAGAR', 10,'II SEM');
Query OK, 1 row affected (0.14 sec)

INSERT INTO STUDENT VALUES ('SCAS202202','MANJULA','BASAVANGUDI', 10,'II SEM');

Query OK, 1 row affected (0.19 sec)

INSERT INTO STUDENT VALUES ('SCAS202203','LAKSHMI','BASAVANGUDI', 10,'IV SEM');

Query OK, 1 row affected (0.11 sec)

INSERT INTO STUDENT VALUES ('SCAC202203','RENUKA','HANUMANTHNAGAR', 20,'II SEM');

Query OK, 1 row affected (0.05 sec)

INSERT INTO STUDENT VALUES ('SCAC202204','ARUN','JPNAGAR', 30,'II SEM');

Query OK, 1 row affected (0.13 sec)

INSERT INTO STUDENT VALUES ('SCAS202204','ABHI','GIRINAGAR', 40,'II SEM');

Query OK, 1 row affected (0.06 sec)

INSERT INTO STUDENT VALUES ('SCAA202201','DEEPTI','GIRINAGAR', 50,'IV SEM');

Query OK, 1 row affected (0.13 sec)

SELECT * FROM STUDENT;

+------------------+------------------+------------------------------+----------------+-----------+
| USN | NAME | ADDRESS | BRANCHID | SEM |
+------------------+------------------+------------------------------+----------------+-----------+
| SCAA202201 | DEEPTI | GIRINAGAR | 50 | IV SEM |
| SCAC202203 | RENUKA | HANUMANTHNAGAR | 20 | II SEM |
| SCAC202204 | ARUN | JPNAGAR | 30 | II SEM |
| SCAS202201 | ANURADHA | JAYANAGAR | 10 | II SEM |
| SCAS202202 | MANJULA | BASAVANGUDI | 10 | II SEM |
| SCAS202203 | LAKSHMI | BASAVANGUDI | 10 | IV SEM |
| SCAS202204 | ABHI | GIRINAGAR | 40 | II SEM |
+------------------+-----------------+-------------------------------+---------------+------------+
7 rows in set (0.00 sec)

BOOK (Bookid, Bookname, Authorid, Publisher, Branchid)

CREATE TABLE BOOK (Bookid varchar2 (10) primary key, Bookname varchar2 (15) not null,
Authorid varchar2 (10) reference author, publisher varchar2 (20) not null, Branchid number
references branch);
DESC BOOK;
+------------------+----------------+------+-----+-------------+-------+
| Field | Type | Null | Key | Default | Extra|
+-----------------+-----------------+-------+------+-----------+-------+
| BOOKID | varchar (10) | NO | PRI | NULL | |
| BOOKNAME| varchar (15) | NO | | NULL | |
| AUTHORID | varchar (10) | YES | | NULL | |
| PUBLISHER | varchar (20) | NO | | NULL | |
| BRANCHID | int (5) | YES | | NULL | |
+-----------------+-----------------+-------+------+-----------+-------+
5 rows in set (0.01 sec)
INSERT INTO BOOK VALUES ('NEPDBMS','DBMS','NEPCOMP02','SKYWARD', 10);
Query OK, 1 row affected (0.13 sec)

INSERT INTO BOOK VALUES ('NEPSE','SE','NEPCOMP02','SKYWARD', 10);

Query OK, 1 row affected (0.13 sec)

INSERT INTO BOOK VALUES ('NEPJAVA','JAVA','NEPCOMM01','OXFORD', 20);

Query OK, 1 row affected (0.14 sec)

INSERT INTO BOOK VALUES ('NEPMATHS','MATHS','NEPSCI01','OXFORD', 30);

Query OK, 1 row affected (0.11 sec)

INSERT INTO BOOK VALUES ('NEPPHY','PHYSICS','NEPCOMM02','SHREE', 40);

Query OK, 1 row affected (0.11 sec)

SELECT * FROM BOOK;

+-----------------+------------------+------------------+----------------+---------------+
| BOOKID | BOOKNAME | AUTHORID | PUBLISHER | BRANCHID |
+-----------------+-----------------+-------------------+---------------+----------------+
| NEPDBMS | DBMS | NEPCOMP02 | SKYWARD | 10 |
| NEPJAVA | JAVA | NEPCOMM01 | OXFORD | 20 |
| NEPMATHS | MATHS | NEPSCI01 | OXFORD | 30 |
| NEPPHY | PHYSICS | NEPCOMM02 | SHREE | 40 |
| NEPSE | SE | NEPCOMP02 | SKYWARD | 10 |
+-----------------+----------------+--------------------+----------------+--------------+
5 rows in set (0.00 sec)

AUTHOR (Authorid, Authername, Country, age)

CREATE TABLE AUTHOR (Authorid varchar2 (10) primary key, Authername varchar2 (15)
not null, country varchar2 (15), Age integer);

DESC AUTHOR;
+---------------------+------------------+------+-------+-----------+---------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+------------------+------+-------+-----------+---------+
| AUTHORID | varchar (10) | NO | PRI | NULL | |
| AUTHERNAME | varchar (15) | NO | | NULL | |
| COUNTRY | varchar (15) | YES | | NULL | |
| AGE | int (11) | YES | | NULL | |
+---------------------+------------------+------+-------+----------+---------+
4 rows in set (0.02 sec)
INSERT INTO AUTHOR VALUES ('NEPCOMP01','ARUNA','INDIA', 36);
Query OK, 1 row affected (0.13 sec)

INSERT INTO AUTHOR VALUES ('NEPCOMP02','SUMA','INDIA', 38);

Query OK, 1 row affected (0.05 sec)

INSERT INTO AUTHOR VALUES ('NEPCOMM02','SANGEETHA','INDIA', 42);

Query OK, 1 row affected (0.11 sec)
INSERT INTO AUTHOR VALUES ('NEPCOMM01','DILIP','INDIA', 39);
Query OK, 1 row affected (0.13 sec)

INSERT INTO AUTHOR VALUES ('NEPSCI01','SHEKAR','INDIA', 44);

Query OK, 1 row affected (0.11 sec)

SELECT * FROM AUTHOR;

+--------------------+---------------------+---------------+-------+
| AUTHORID | AUTHERNAME | COUNTRY | AGE |
+--------------------+---------------------+---------------+-------+
| NEPCOMM01 | DILIP | INDIA | 39 |
| NEPCOMM02 | SANGEETHA | INDIA | 42 |
| NEPCOMP01 | ARUNA | INDIA | 36 |
| NEPCOMP02 | SUMA | INDIA | 38 |
| NEPSCI01 | SHEKAR | INDIA | 44 |
+-------------------+---------------------+----------------+-------+
5 rows in set (0.00 sec)

BORROW (USN, Bookid, Borrowed_Date)

CREATE TABLE BORROS (USN Varchar2 (15) references student, Bookid varchar2 (10)
references book, Borrowed_Date date, primary key (USN, Bookid));

DESC BORROW;

+--------------------------+-----------------+------+------+-----------+---------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------+------+------+------------+--------+
| USN | varchar (15) | NO | PRI | | |
| BOOKID | varchar (10) | NO | PRI | | |
| BORROWED_DATE | date | YES | | NULL | |
+--------------------------+-----------------+------+-------+-----------+--------+
3 rows in set (0.02 sec)

INSERT INTO BORROW VALUES ('SCAS202201','NEPDBMS','2022/05/20');

Query OK, 1 row affected (0.05 sec)

INSERT INTO BORROW VALUES ('SCAS202201','NEPSE','2022/05/28');

Query OK, 1 row affected (0.11 sec)

INSERT INTO BORROW VALUES ('SCAC202204','NEPMATHS','2022/06/06');

Query OK, 1 row affected (0.11 sec)

INSERT INTO BORROW VALUES ('SCAA202201','NEPPHY','2022/06/12');

Query OK, 1 row affected (0.13 sec)

INSERT INTO BORROW VALUES ('SCAS202203','NEPPHY','2022/06/12');

Query OK, 1 row affected (0.13 sec)

INSERT INTO BORROW VALUES ('SCAS202201','NEPMATHS','2022/06/05');

Query OK, 1 row affected (0.11 sec)
INSERT INTO BORROW VALUES ('SCAS202201','NEPJAVA','2022/06/05');
Query OK, 1 row affected (0.14 sec)

SELECT * FROM BORROW;

+------------------+-----------------+--------------------------+
| USN | BOOKID | BORROWED_DATE |
+------------------+-----------------+--------------------------+
| SCAA202201 | NEPPHY | 2022-06-12 |
| SCAC202204 | NEPMATHS | 2022-06-06 |
| SCAS202201 | NEPDBMS | 2020-05-22 |
| SCAS202201 | NEPJAVA | 2022-06-05 |
| SCAS202201 | NEPMATHS | 2022-06-05 |
| SCAS202201 | NEPSE | 2022-05-28 |
| SCAS202203 | NEPPHY | 2022-06-18 |
+------------------+----------------+--------------------------+
7 rows in set (0.00 sec)
1. Perform the following:
a. Viewing all databases
Show Databases;
b. Creating a Database

Create Database Databasename.

Example:
Create Database colleagedb;

c. Viewing all Tables in a Database

Show tables;
+------------------------------+
| Tables_in_colleagedb |
+------------------------------+
| author |
| book |
| borrow |
| branch |
| student |
+-----------------------------+
5 rows in set (0.00 sec)

d. Creating Tables (with and Without Constraints)

Create table tablename (attributes type (size)…..);

Example
Create table student (sno integer, sname char (20), dbirth date);

e. Inserting the records

Insert into tablename values (values…..);

Example
Insert into student (101,’rajesh’,’1969-03-06’);

f. Updating the records

Update tablename set attributename=”value”;

Example
Update student set sname=”rajeshrao’;

g. Deleting the records

Delete student where sno=’101’;
h. Saving (Commit)
Commit;
i. Undoing (Rollback)
Rollback;
 2. Execute the following queries:
a. List the details of students who are all studying in 2nd sem MCA.
SELECT * FROM STUDENT S, BRANCH B WHERE [Link]=[Link] AND
[Link]='II SEM' AND [Link]='MCA';

USN NAME ADDRESS BRANCHID SEM BRANCH BRANCHNAME HOD

ID
SCAS202201 ANURADH JAYANAGAR 10 II SEM 10 MCA SUDHA M
A
SCAS202202 MANJULA BASAVANG 10 II SEM 10 MCA SUDHA M
UDI

2 rows in set (0.00 sec)

b. List the students who are not borrowed any books.

SELECT * FROM STUDENT S WHERE [Link] NOT IN (SELECT [Link] FROM BORROW B);
+------------------+---------------+---------------------------+----------------+--------+
| USN | NAME | ADDRESS | BRANCHID | SEM |
+------------------+--------------+-----------------------------+---------------+--------+
| SCAC202203 | RENUKA | HANUMANTHNAGAR | 20 | II SEM |
| SCAC202204 | ARUN | JPNAGAR | 30 | II SEM |
| SCAS202202 | MANJULA | BASAVANGUDI | 10 | II SEM |
| SCAS202204 | ABHI | GIRINAGAR | 40 | II SEM |
+------------------+--------------+----------------------------+---------------+--------+
4 rows in set (0.11 sec)

3. a. Display the USN, Student_name, Branch_name, Book_name, Author_name,

Books_Borrowed_Date of 2nd sem MCA students who borrowed books
SELECT [Link], [Link], [Link], [Link], [Link], [Link],
B.BORROWED_DATE FROM STUDENT S, BRANCH BR, BOOK BK, AUTHOR A, BORROW B
WHERE [Link]=[Link] AND [Link]=[Link] AND [Link]=[Link]
AND [Link]=[Link] AND [Link]='II SEM' AND [Link]='MCA';
+-----------------+------------------+--------+----------------+-----------+------------+--------------------------+
| USN | NAME | SEM | BRANAME | BNAME | ANAME | BORR-OWED_DATE |
+------------------+-----------------+--------+---------------+----------+------------+----------------------------+
| SCAS202201 | ANURADHA | II SEM | MCA | DBMS | SUMA | 2020-05-22 |
| SCAS202201 | ANURADHA | II SEM | MCA | JAVA | DILIP | 2022-06-05 |
| SCAS202201 | ANURADHA | II SEM | MCA | MATHS | SHEKAR | 2022-06-05 |
| SCAS202201 | ANURADHA | II SEM | MCA | SE | SUMA | 2022-05-28 |
+------------------+-----------------+---------+-------------+------------+------------+--------------------------+
4 rows in set (0.00 sec)
 b. Display the student details who borrowed books of more than one Author.
SELECT [Link], COUNT(DISTINCT [Link]) AS "NO OF BOOKS" FROM
AUTHOR A, BOOK BK WHERE [Link]=[Link] GROUP BY [Link];
+------------------+-----------------------+
| AUTHERNAME | NO OF BOOKS |
+------------------+-----------------------+
| DILIP | 1 |
| SANGEETHA | 1 |
| SHEKAR | 1 |
| SUMA | 2 |
+------------------+-----------------------+
4 rows in set (0.06 sec)
4. a. Display the student details who borrowed more than two books.
SELECT [Link] FROM STUDENT S, BORROW B
WHERE [Link]=[Link]
GROUP BY [Link]
HAVING COUNT (DISTINCT [Link]) > 2;
+-----------------+
| NAME |
+-----------------+
| ANURADHA |
+-----------------+
1 row in set (0.00 sec)
b. Display the student details who borrowed books of more than one Author;

SELECT [Link], COUNT (DISTINCT [Link]) FROM STUDENT S, BOOK BK, BORROW B
WHERE [Link]=[Link] AND [Link] = [Link] GROUP BY [Link]
HAVING COUNT (DISTINCT [Link]) > 1;
+-----------------+-----------------------------------------+
| NAME | COUNT (DISTINCT [Link]) |
+-----------------+------------------------------------------+
| ANURADHA | 4 |
+-----------------+------------------------------------------+
1 row in set (0.00 sec) 3 rows in set (0.14 sec)

5. a. Display the book names in descending order of their names.

SELECT * FROM BOOK ORDER BY BOOKNAME DESC;
+----------------+------------------+-------------------+---------------+----------------+
| BOOKID | BOOKNAME | AUTHORID | PUBLISHER | BRANCHID |
+----------------+------------------+-------------------+----------------+---------------+
| NEPSE | SE | NEPCOMP02 | SKYWARD | 10 |
| NEPPHY | PHYSICS | NEPCOMM02 | SHREE | 40 |
| NEPMATHS | MATHS | NEPSCI01 | OXFORD | 30 |
| NEPJAVA | JAVA | NEPCOMM01 | OXFORD | 20 |
| NEPDBMS | DBMS | NEPCOMP02 | SKYWARD | 10 |
+----------------+-----------------+--------------------+----------------+---------------+
5 rows in set (0.00 sec)
 b. List the details of students who borrowed the books which are all published by the same
publisher.
SELECT [Link], COUNT ([Link]) FROM STUDENT S, BOOK BK, BORROW B
WHERE [Link]=[Link] AND [Link]=[Link]
GROUP BY [Link];
+-----------------+--------------------------------+
| NAME | COUNT ([Link]) |
+-----------------+--------------------------------+
| ANURADHA | 4 |
| DEEPTI | 1 |
| LAKSHMI | 1 |
+-----------------+---------------------------------+
2 rows in set (0.00 sec)

Consider the following schema:

STUDENT2 (USN, name, date_of_birth, branch, mark1, mark2, mark3, total, GPA);

6. Perform the following:

a. Creating Tables (with Constraints).

CREATE TABLE STUDENT2

(USN VARCHAR(10) PRIMARY KEY,
NAME VARCHAR(20) NOT NULL,
DOB DATE,
BRANCH VARCHAR(10) NOT NULL,
MARK1 INTEGER(3) NOT NULL,
MARK2 INTEGER(3) NOT NULL,
MARK3 INTEGER(3) NOT NULL,
TOTAL INTEGER(4),
GPA DECIMAL(4,2));
Query OK, 0 rows affected (0.27 sec)

b. Inserting the value in the record

INSERT INTO STUDENT2 VALUES('SCA202201','SANJANA','2004-08-
24','BCA',85,96,97,NULL,NULL);
Query OK, 1 row affected (0.12 sec)

INSERT INTO STUDENT2 VALUES('SCAC202201','ANIRUDH','2004-10-

10','BCOM',75,85,65,NULL,NULL);
Query OK, 1 row affected (0.02 sec)

INSERT INTO STUDENT2 VALUES('SCAB202201','AKASH','2004-11-

10','BBA',75,85,83,NULL,NULL);
Query OK, 1 row affected (0.13 sec)
 INSERT INTO STUDENT2 VALUES('SCA202202','TANDRA','2004-12-
01','BCA',84,56,63,NULL,NULL);
Query OK, 1 row affected (0.13 sec)

INSERT INTO STUDENT2 VALUES('SCA202203','ANUSHA','2005-01-

01','BCA',68,72,78,NULL,NULL);
Query OK, 1 row affected (0.03 sec)

c. Display the entered value in student2 table.

SELECT * FROM STUDENT2;

+-----------------+--------------+----------------+--------+-------+-------+-------+-------+------+
| USN | NAME | DOB| BRANCH |MARK1 |MARK2 |MARK3|TOTAL| GPA |
+-----------------+--------------+-----------------+------- +-------+-------+-------+-------+------+
| SCA202201 | SANJANA | 2004-08-24 | BCA | 85 | 96 | 97 | NULL | NULL |
| SCA202202 | TANDRA | 2004-12-01 | BCA | 84 | 56 | 63 | NULL | NULL |
| SCA202203 | ANUSHA | 2005-01-01 | BCA | 68 | 72 | 78 | NULL | NULL |
| SCAB202201 | AKASH | 2004-11-10 | BBA | 75 | 85 | 83 | NULL | NULL |
| SCAC202201 | ANIRUDH | 2004-10-10 |BCOM| 75 | 85 | 65 | NULL | NULL |
+------------+---------+------------+--------+-------+-------+-------+-------+------+
5 rows in set (0.00 sec)

d. Updating the record (for calculate Total)

UPDATE STUDENT2 SET TOTAL = MARK1 + MARK2 + MARK3;
Query OK, 0 rows affected (0.14 sec)
Rows matched: 5 Changed: 0 Warnings: 0
SELECT * FROM STUDENT2;
+-----------------+----------+------------+--------+-------+-------+-------+-------+------+
| USN| NAME |DOB |BRANCH |MARK1|MARK2|MARK3|TOTAL|GPA |
+----------------+------------+------------+--------+-------+-------+-------+-------+------+
| SCA202201| SANJANA | 2004-08-24 | BCA| 85 | 96 | 97 | 278 | NULL |
| SCA202202| TANDRA | 2004-12-01 | BCA | 84 | 56 | 63 | 203 | NULL|
| SCA202203| ANUSHA | 2005-01-01 | BCA | 68 | 72 | 78 | 218 | NULL|
| SCAB202201 | AKASH | 2004-11-10 | BBA |75 | 85 | 83 | 243 | NULL|
| SCAC202201| ANIRUDH|2004-10-10| BCOM| 75 | 85 | 65 |225|NULL|
+------------------+----------+------------+--------+-------+-------+-------+-------+------+
5 rows in set (0.01 sec)

7. Execute the following queries:

a. Find the GPA score of all the students.
UPDATE STUDENT2 SET GPA = (TOTAL*100)/300;
Query OK, 5 rows affected, 3 warnings (0.05 sec)
Rows matched: 5 Changed: 5 Warnings: 3
 SELECT * FROM STUDENT2;
+-----------------+----------------+----------------+-------+-------+------------+------+-------+-------+
| USN | NAME | DOB | BRANCH |MARK1| MARK2| MARK3|TOTAL|GPA |
+-----------------+----------------+-----------------+------+-------+------------+-------+-------+---------+
| SCA202201 | SANJANA | 2004-08-24 | BCA | 85 | 96 | 97 | 278 | 92.67 |
| SCA202202 | TANDRA | 2004-12-01 | BCA | 84 | 56 | 63 | 203 | 67.67 |
| SCA202203 | ANUSHA | 2005-01-01 | BCA | 68 | 72 | 78 | 218 | 72.67 |
| SCAB202201 | AKASH | 2004-11-10 | BBA | 75 | 85 | 83 | 243 | 81.00 |
| SCAC202201 | ANIRUDH | 2004-10-10 |BCOM| 75 | 85 | 65 | 225 | 75.00 |
+------------+---------+------------+--------+-------+-------+-------+-------+------------------------------- +
5 rows in set (0.00 sec)

b. Find the students who born on a particular year of birth from the date of birth column
SELECT USN, NAME, BRANCH, DOB FROM STUDENT2 WHERE DOB LIKE '2004%';
+------------------+-------------+-------------+----------------+
| USN | NAME | BRANCH | DOB |
+------------------+--------------+------------+----------------+
| SCA202201 | SANJANA | BCA | 2004-08-24 |
| SCA202202 | TANDRA | BCA | 2004-12-01 |
| SCAB202201 | AKASH | BBA | 2004-11-10 |
| SCAC202201 | ANIRUDH | BCOM | 2004-10-10 |
+------------------+--------------+------------+-----------------+
3 rows in set, 1 warning (0.00 sec)

8. a. List the students who are studying in a particular branch of study.

SELECT USN,NAME, BRANCH, DOB FROM STUDENT2 WHERE BRANCH='BCA';
+----------------+---------------+-------------+----------------+
| USN | NAME | BRANCH | DOB |
+----------------+---------------+-------------+----------------+
| SCA202201 | SANJANA | BCA | 2004-08-24 |
| SCA202202 | TANDRA | BCA | 2004-12-01 |
| SCA202203 | ANUSHA | BCA | 2005-01-01 |
+----------------+---------------+------------+-----------------+
4 rows in set (0.00 sec)
b. Find the maximum GPA score of the student branch-wise
SELECT BRANCH, MAX (GPA) FROM STUDENT2 GROUP BY BRANCH;
+------------+----------------+
|BRANCH | MAX (GPA) |
+------------+----------------+
| BBA | 81.00 |
| BCA | 92.67 |
| BCOM | 75.00 |
+------------+----------------+
3 rows in set (0.01 sec)
9. a. Find the students whose name starts with the alphabet “S”
SELECT * FROM STUDENT2 WHERE NAME LIKE 'S%';
+-----------+---------+------------+--------------------+-------+-------+-------+-------+-------+
| USN | NAME| DOB| BRANCH | MARK1| MARK2| MARK3| TOTAL| GPA |
+-----------+---------+------------+---------------------+-------+-------+-------+-------+-------+
| SCA202201| SANJANA| 2004-08-24 | BCA | 85 | 96 | 97 | 278 | 92.67 |
+-----------+------------------+-----------------+-------+------+-------+-------+-------+-------+
1 row in set (0.00 sec)

b. Update the column total by adding the columns mark1, mark2, mark3.

UPDATE STUDENT2 SET TOTAL = MARK1 + MARK2 + MARK3;

Query OK, 0 rows affected (0.14 sec)
Rows matched: 5 Changed: 0 Warnings: 0

10. Execute the following quires:

a. Find the students whose name ends with the alphabets “AR”.
SELECT NAME FROM STUDENT2 WHERE NAME LIKE ‘%AR”;
Empty set (0.00 sec)

Find the students whose address ends with the alphabets “AR”.
SELECT * FROM STUDENT WHERE ADDRESS LIKE '%AR';
+-----------------+------------------+-----------------------------+----------------+----------+
| USN | NAME | ADDRESS | BRANCHID | SEM |
+------------------+-----------------+------------------------------+---------------+-----------+
| SCAA202201 | DEEPTI | GIRINAGAR | 50 | IV SEM |
| SCAC202203 | RENUKA | HANUMANTHNAGAR | 20 | II SEM |
| SCAC202204 | ARUN | JPNAGAR | 30 | II SEM |
| SCAS202201 | ANURADHA | JAYANAGAR | 10 | II SEM |
| SCAS202204 | ABHI | GIRINAGAR | 40 | II SEM |
+------------------+------------------+-----------------------------+---------------+----------+
5 rows in set (0.00 sec)

b. Delete the student details whose USN is greater than 1001.

SELECT * FROM STUDENT WHERE USN > '1001';

+-----------------+------------------+------------------------+----------+--------+
| USN | NAME | ADDRESS | BRANCHID |SEM|
+------------------+-----------------+------------------------+----------+--------+
| SCAA202201 | DEEPTI | GIRINAGAR | 50 | IV SEM |
| SCAC202203 | RENUKA | HANUMANTHNAGAR| 20 | II SEM |
| SCAC202204 | ARUN | JPNAGAR | 30 | II SEM |
| SCAS202201 | ANURADHA | JAYANAGAR | 10 | II SEM |
| SCAS202202 | MANJULA | BASAVANGUDI | 10 | II SEM |
| SCAS202203 | LAKSHMI | BASAVANGUDI | 10 | IV SEM |
| SCAS202204 | ABHI | GIRINAGAR | 40 | II SEM |
+------------------+-----------------+-------------------------+----------+--------+
7 rows in set (0.00 sec)