Screenshot 2024-11-10 at 1.06.21 PM

St.
mark’S
practical file
informatics practices
made by - himanshi
class – xi - e
Ques 1. Consider a database LOANS with
the following table:
Write SQL Commands for the tasks:

CREATE DATABASE AND USE IT
1. Create the database LOANS.
2. use the database LOANS.
CREATE TABLE/INSERT INTO

1.Create the table Loan _Accounts and
insert tuples in it.
SIMPLE SELECT
1. Display the details of all the loans.
2. Display the Account No, Cust_ Name
and Loan_ amount of all the loans.
CONDITIONAL SELECT USING

WHERE CLAUSE
1. Display the details of all the loans with
less than 40 Instalments.
2. Display the Account No and Loan_
Amount of all the loans started before 01-
04-2009.
3. Display the Int_ Rate of all the loans

started after 01-04-2009.
USING NULL
1. Display the details of all the loans
whose rate of interest is NULL.
whose rate of interest is NOT NULL.
USING DISTINCT CLAUSE

1. Display the amounts of various loans
from the table Loan_ Account. A loan
amount should appear only once.
2. Display the number of instalments of
various loans from the table Loan_
Accounts. An instalment should appear
only once.
USING LOGICAL OPERATORS (NOT,

AND, OR)
1. Display the Cust_ Name and Loan_
Amount for all the loans for which the
loan amount is less than 500000 or Int_
Rate is more than 12.
2. Display the details of all the loans which
started in the year 2009.

whose Loan_ Amounts is in the range
400000 to 500000.

whose rate of interest is in the range 11%
to 12%.
USING IN OPERATOR
1. Display the Cust_ Name and Loan_
Amount for all the loans for which the
number of instalments is 24, 36 or 48.
USING BETWEEN OPERATOR

whose Loan_ Amount is in the range
400000 to 500000.
whose rate of interest is in the range 11%
to 12%.
USING LIKE OPERATOR

and Loan_ Amount for all the loans for
which the Cust_ Name ends with ‘Sharma’.

which the Cust_ Name ends with ‘a’.
which the Cust_ Name contains ‘a’.

which the Cust_ Name does not contain
‘P’.
which the Cust_ Name contains ‘a’ as the
second last character.
USING ORDER BY CLAUSE

1. Display the details of all the loans in the
ascending order of their Loan_ Amount.

descending order of their Start_ Date.
ascending order of their Loan_ Amount
and within Loan_ Amount in the
descending order of their Start_ Date.
USING UPDATE, DELETE, ALTER

TABLE
1. Put the interest rate 11.50% for all the
loans for which interest is NULL.
2. Increase the interest rate by 0.5% for all
the loans for which the loan amount is
more than 400000.
3.Delete the records of all the loans whose

start date is before 2007.
4. Delete the records of all the loans of
‘K.P. Jain’.
5. Add another column Category of type

CHAR (1) in the Loan table.
Ques 2. Create a table WORKER and add
records as shown below.
CREATE/ USE/ INSERT

1. To display the details in ascending
order of salary.
2. To display Emp No, Emp Name in

descending order of Hire date.
3. To display the details in ascending
order of Emp No.
Ques 3. Create a table EMPLOYEE with the

following data.
CREATE/ USE/ INSERT
1. To display the list of all employees in
ascending order of salary.
2. To display the employee names in

descending order of age.
3. To display the grade and number of

employees in each grade.
Ques 4. Create the following tables
PRODUCT and CLIENT with the following
data.
Table- PRODUCT
Table- CLIENT
CREATE/ USE/ INSERT

1. To display the details of those clients
whose city is Delhi.
2. To display the details of the products

whose price is in the range 50 to 100.
3. To increase the price of all products by
10.
4. SELECT DISTINCT City FROM Clients;

5. SELECT ProductName, Price*4 FROM
Product WHERE P_ID = ‘TP01’ OR P_ID =
‘SH06’;
Ques 5. Create a table WORKER with the

following data.
CREATE/ USE/ INSERT

1. To display the details of all WORKERS in
descending order of DOB.
2. To display NAME and DESIG of those

WORKERS whose P_LEVEL is either P001
or P002.
3. To display the content of all the

WORKERS whose DOB is in between ’19-
JAN-1984’ and ’18-JAN-1987’.
4. To add a new row with the following:
19,’Daya Kishore’, ’Operator’, ’P003’, ’19-
Jun-2008’, ’11-Jul-1984’

Screenshot 2024-11-10 at 1.06.21 PM

Uploaded by

Copyright:

Available Formats

Screenshot 2024-11-10 at 1.06.21 PM

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

Screenshot 2024-11-10 at 1.06.21 PM

Uploaded by

Copyright:

Available Formats

St.

Write SQL Commands for the tasks:

2. use the database LOANS.

CREATE TABLE/INSERT INTO

CONDITIONAL SELECT USING

3. Display the Int_ Rate of all the loans

USING DISTINCT CLAUSE

USING LOGICAL OPERATORS (NOT,

3. Display the details of all the loans

4. Display the details of all the loans

USING BETWEEN OPERATOR

USING LIKE OPERATOR

2. Display the Account No, Cust_ Name

4. Display the Account No, Cust_ Name

USING ORDER BY CLAUSE

2. Display the details of all the loans in the

USING UPDATE, DELETE, ALTER

3.Delete the records of all the loans whose

5. Add another column Category of type

CREATE/ USE/ INSERT

2. To display Emp No, Emp Name in

Ques 3. Create a table EMPLOYEE with the

2. To display the employee names in

3. To display the grade and number of

CREATE/ USE/ INSERT

2. To display the details of the products

4. SELECT DISTINCT City FROM Clients;

Ques 5. Create a table WORKER with the

CREATE/ USE/ INSERT

2. To display NAME and DESIG of those

3. To display the content of all the

You might also like