Sudhakar

Topics: SQL, Personal name, Given name Pages: 14 (1718 words) Published: January 5, 2014
 CHAPTER- MySQL
Lab exercise
1. Consider the following table named "GYM" with details about Fitness products being sold in the store. Table Name: GYM
PrCode stores Codes of Products
PrName stores names of Products
(UnitPrice is in Rs.)
PrCode
PrName
UnitPrice
Manufacturer
P101
Cross Trainer
25000
Avon Fitness
P102
TreadMill
32000
AG Fitline
P103
MassageChair
20000
Fit Express
P104
Vibration Trainer
22000
Avon Fitness
P105
Bike
13000
Fit Express

Write SQL statements to do the following:
a) Display the names of all the products in the store.

b) Display the names and unit price of all the products in the store

c) Display the names of all the products with unit price less than Rs.20000.00

d) Display details of all the products with unit price in the range 20000 to 30000

e) Display names of all products by the manufacturer "Fit Express"

f) Display all rows sorted in descending order of unit price.

g) Add a new row for product with the details: "P106","Vibro Exerciser", 23000, manufacturer: "Avon Fitness".

h) Change the Unit Price data of all the rows by applying a 10% discount reduction on all the products.

i)Display details of all products with manufacturer name starting with "A"

2. Consider the following tables Employee and Department.

Employee
Ecode
LastName
FirstName
Department
101
Sharma
Amit
Sales
102
Arora
Shiv
Personnel
103
Lakshmi
KS
Accounts
104
Rajlani
Shivika
Accounts
105
Thakral
Satvik
Sales

Department
DepCode
DepName
Budget
101
Sales
200000
102
Personnel
150000
104
Accounts
300000
Write SQL statements to do the following:

a) Display the last names and first names of all employees.

b) Display the Department names of all employees, without duplicates.

c) Display all the details of employees with last name as "Lakshmi".

d) Display all the details of employees whose last name is ""Rajlani" or "Sharma".

e) Display the codes and first names of all employees of 'Accounts' department.

f) Display department names of departments with budget above 18000.

g) Display all the details of employees whose First name begins with "S".

h) Display department details(from Department table) in descending order of Budget amount.

i) Change the Department name "Sales" to "Marketing" everywhere in the table "Employee" and "Department"

j) Add a new row with appropriate data values in Department table.

k) Create the table Department with columns of appropriate data types.

CHAPTER – Functions in MySQL
Lab Exercises
3. Create the following table named "Charity" and write SQL queries for the tasks that follow: Table: Charity
P_Id
LastName
FirstName
Address
City
Contribution
1
Bindra
Jaspreet
5B, Gomti Nagar

Lucknow

3500.50

2

Rana

Monica

21 A, Bandra

Mumbai

2768.00

3

Singh

Jatinder

8, Punjabi Bagh

Delhi

2000.50

4

Arora

Satinder

K/1, Shere Punjab Colony

Mumbai

1900.00

5

Krishnan
Vineeta
A-75,Adarsh Nagar

(Contribution is in Rs.)
I. Display all first names in lowercase

II. Display all last names of people of Mumbai city in uppercase

III. Display Person Id along with First 3 characters of his/her name.

IV. Display first name concatenated with last name for all the employees.

V. Display length of address along with Person Id

VI. Display last 2 characters of City and Person ID.

VII. Display Last Names and First names of people who have "at" in the second or third position in their first names.

VIII. Display the position of 'a' in Last name in every row.

IX. Display Last Name and First name of people who have "a" as the last character in their First names.

X. Display the...
Continue Reading

Please join StudyMode to read the full document

Become a StudyMode Member

Sign Up - It's Free