Practical Record SQL

SQL
17. Create a table Student with the following fields and insert at least 5 records into the
table except for the column Total.
Roll_Number Integer Primary key
Name Varchar (25)
Batch Varchar (15)
Mark1 Integer
Mark2 Integer
Mark3 Integer
Total Integer
a. Update the column Total with the sum of Mark1, Mark2 and Mark3.
b. List the details of students in Commerce batch.
c. Display the name and total marks of students who are failed (Total < 90).
d. Display the name of students in alphabetical order and in batch based.
CREATE TABLE STUDENT (ROLL INT PRIMARY KEY,NAME VARCHAR(25), BATCH

VARCHAR(15),MARK1 INT,MARK2 INT,MARK3 INT,TOTAL INT);
INSERT INTO STUDENT (ROLL,NAME,BATCH,MARK1,MARK2,MARK3) VALUES

(1,'SANJEEV','COMMERCE',56,76,78);
A. UPDATE STUDENT SET TOTAL=MARK1+MARK2+MARK3;
B. SELECT * FROM STUDENT WHERE BATCH=‘COMMERCE’;
C. SELECT NAME, TOTAL FROM STUDENT WHERE TOTAL<90;
D. SELECT NAME, BATCH FROM STUDENT GROUP BY BATCH ORDER BY NAME;

18. Create a table Employee with the following fields and insert at least 5 records into the
table except the column Gross_pay and DA.
Emp_code Integer Primary key
Emp_name Varchar (20)
Designation Varchar (25)
Department Varchar (25)
Basic Decimal (10,2)
DA Decimal (10,2)
Gross_pay Decimal (10,2)
a. Update DA with 75% of Basic.

b. Display the details of employees in Purchase, Sales and HR departments.
c. Update the Gross_pay with the sum of Basic and DA

d. Display the details of employee with gross pay below 10000.
CREATE TABLE EMPLOYEE(ECD INT PRIMARY KEY, ENAME VARCHAR(25), DESIG

VARCHAR(25), DEPT VARCHAR(25), BASIC DECIMAL(10,2), DA DECIMAL(10,2),
GROSS DECIMAL(10,2));
INSERT INTO EMPLOYEE (ECD,ENAME,DESIG,DEPT,BASIC) VALUES

(1,'RAJEEV','MANAGER','FINANCE',20000);
SELECT * FROM EMPLOYEE;
a. UPDATE EMPLOYEE SET DA=BASIC*0.75;
b. SELECT * FROM EMPLOYEE WHERE DEPARTMENT IN (‘PURCHASE’,’SALES’,’HR’);
c. UPDATE EMPLOYEE SET GROSS_PAY =BASIC +DA;
d. SELECT * FROM EMPLOYEE WHERE DESIGNATION=’CLERK’ AND BASIC<5000;

19. Create a table Stock, which stores daily sales of items in a shop, with the following fields
and insert at least 5
records into the table.
Item_code Integer Primary key
Item_name Varchar (20)
Manufacturer_Code Varchar (5)
Qty Integer
Unit_Price Decimal (10,2)
a. Display the item names with stock zero.

b. Display the number of items manufactured by the same manufacturer.
c. Display the highest price and lowest quantity in the stock.
d. Increase the unit price of all items by 10%.
CREATE TABLE STOCK(ICODE INT PRIMARY KEY,INAME VARCHAR(20), MCODE VARCHAR(5),

QTY INT, PRICE DECIMAL(10,2),EXD DATE);
INSERT INTO STOCK VALUES (1,'BAG','PUMA',10,1000,'2017-02-28');
SELECT * FROM STOCK;
A. SELECT ITEM_NAME FROM STOCK WHERE QTY = 0;

B. SELECT MANUFACTURER_CODE,COUNT(*) FROM STOCK GROUP BY
MANUFACTURER_CODE;
C. SELECT MAX(UNIT_PRICE),MIN(QTY) FROM STOCK;
D. UPDATE STOCK SET UNIT_PRICE = UNIT_PRICE + UNIT_PRICE*0.1;
20. Create a table Bank with the following fields and insert at least 5 records into the table.
Acc_No Integer Primary key
Acc_Name Varchar (20)
Branch_Name Varchar (25)
Acc_ Type Varchar (10)
Amount Decimal (10,2)
A. Display the account details of ‘Savings Account’ in Kollam branch.

B. Change the branch name ‘Trivandrum’ to ‘Thiruvananthapuram’.
C. Display the details of customers in Thiruvananthapuram, Ernakulam and Kozhikode.
D. List the details of customers in Thrissur branch having a minimum balance of Rs. 5000.
CREATE TABLE BANK(ACNO INT PRIMARY KEY,ACNAME VARCHAR(20), BRANCH

VARCHAR(20),ACTYPE VARCHAR(10),AMOUNT DECIMAL(10,2));
INSERT INTO BANK VALUES (201,'SUDEEP','KOTTAYAM','SB',10000);
A. SELECT * FROM BANK WHERE ACC_TYPE = ‘SAVINGS ACCOUNT’ AND BRANCH_NAME

=‘KOLLAM’;
B. UPDATE BANK SET BRANCH_NAME=‘THIRUVANANTHAPURAM’ WHERE BRANCH_NAME

= ‘TRIVANDRUM’;
C. SELECT * FROM BANK WHERE BRANCH_NAME

IN(‘THIRUVANANTHAPURAM’,’ERNAKULAM’,’KOZHIKODE’);
D. SELECT * FROM BANK WHERE BRANCH_NAME = ‘THRISSUR’ AND AMOUNT<=5000;

Practical Record SQL

Uploaded by

Copyright:

Available Formats

Practical Record SQL

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Practical Record SQL

Uploaded by

Copyright:

Available Formats

SQL

CREATE TABLE STUDENT (ROLL INT PRIMARY KEY,NAME VARCHAR(25), BATCH

INSERT INTO STUDENT (ROLL,NAME,BATCH,MARK1,MARK2,MARK3) VALUES

A. UPDATE STUDENT SET TOTAL=MARK1+MARK2+MARK3;

B. SELECT * FROM STUDENT WHERE BATCH=‘COMMERCE’;

C. SELECT NAME, TOTAL FROM STUDENT WHERE TOTAL<90;

D. SELECT NAME, BATCH FROM STUDENT GROUP BY BATCH ORDER BY NAME;

a. Update DA with 75% of Basic.

c. Update the Gross_pay with the sum of Basic and DA

CREATE TABLE EMPLOYEE(ECD INT PRIMARY KEY, ENAME VARCHAR(25), DESIG

INSERT INTO EMPLOYEE (ECD,ENAME,DESIG,DEPT,BASIC) VALUES

SELECT * FROM EMPLOYEE;

a. UPDATE EMPLOYEE SET DA=BASIC*0.75;

b. SELECT * FROM EMPLOYEE WHERE DEPARTMENT IN (‘PURCHASE’,’SALES’,’HR’);

c. UPDATE EMPLOYEE SET GROSS_PAY =BASIC +DA;

d. SELECT * FROM EMPLOYEE WHERE DESIGNATION=’CLERK’ AND BASIC<5000;

a. Display the item names with stock zero.

CREATE TABLE STOCK(ICODE INT PRIMARY KEY,INAME VARCHAR(20), MCODE VARCHAR(5),

INSERT INTO STOCK VALUES (1,'BAG','PUMA',10,1000,'2017-02-28');

SELECT * FROM STOCK;

A. SELECT ITEM_NAME FROM STOCK WHERE QTY = 0;

A. Display the account details of ‘Savings Account’ in Kollam branch.

CREATE TABLE BANK(ACNO INT PRIMARY KEY,ACNAME VARCHAR(20), BRANCH

INSERT INTO BANK VALUES (201,'SUDEEP','KOTTAYAM','SB',10000);

A. SELECT * FROM BANK WHERE ACC_TYPE = ‘SAVINGS ACCOUNT’ AND BRANCH_NAME

B. UPDATE BANK SET BRANCH_NAME=‘THIRUVANANTHAPURAM’ WHERE BRANCH_NAME

C. SELECT * FROM BANK WHERE BRANCH_NAME

D. SELECT * FROM BANK WHERE BRANCH_NAME = ‘THRISSUR’ AND AMOUNT<=5000;

You might also like