Scribd
Upload
284 views7 pages

Supplementary Assignment

The document provides SQL queries to perform various operations on a Supplier table. It includes queries to: 1. List names and prices between 3000-7000 2. Update the price of "Keyboard" to 1200 3. Delete rows with stock between 20-40 4. Add a quantity column 5. Display names starting with "S" with name, price, stock 6. Display unique suppliers 7. Insert a new row 8. Increase stock for Samsung suppliers 9. Display details for Philips with stock over 40 10. Display name, supplier, stock for keyboards and mice 11. Display names containing "oa" or "ou" 12. List names and prices

Uploaded by

ghadagekarsatbit19
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
284 views7 pages

Supplementary Assignment

The document provides SQL queries to perform various operations on a Supplier table. It includes queries to: 1. List names and prices between 3000-7000 2. Update the price of "Keyboard" to 1200 3. Delete rows with stock between 20-40 4. Add a quantity column 5. Display names starting with "S" with name, price, stock 6. Display unique suppliers 7. Insert a new row 8. Increase stock for Samsung suppliers 9. Display details for Philips with stock over 40 10. Display name, supplier, stock for keyboards and mice 11. Display names containing "oa" or "ou" 12. List names and prices

Uploaded by

ghadagekarsatbit19
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 7

( SQL Queries)

1. Write the correct SQL commands for the following on the basis of table Supplier.

Creating the table and Insertion of records


Code:
-- Create the Supplier table
CREATE TABLE Supplier (
No INT PRIMARY KEY,
Name VARCHAR(25),
Price INT,
Supplier VARCHAR(25),
Stock INT
);

-- Insert data into the Supplier table


INSERT INTO Supplier (No, Name, Price, Supplier, Stock)
VALUES
(1, 'Motherboard', 7000, 'Intel', 20),
(2, 'Keyboard', 1000, 'TVSE', 70),
(3, 'Mouse', 500, 'Logitech', 60),
(4, 'Soundcard', 600, 'Samsung', 50),
(5, 'Speaker', 600, 'Samsung', 25),
(6, 'Monitor', 3000, 'Philips', 22),
(7, 'CD-ROM', 2800, 'Creative', 32),
(8, 'Printer', 7900, 'HP', 10);
Output:

a. List all Name and Price with price between 3000 and 7000
Code:

SELECT Name, Price


FROM Supplier
WHERE Price BETWEEN 3000 AND 7000;

Output:
b. To set the price to 1200 for ‘Keyboard’
Code:

UPDATE Supplier
SET Price = 1200
WHERE Name = 'Keyboard';

Output:

c. To delete rows with stock between 20 and 40.


Code:
DELETE FROM Supplier
WHERE Stock BETWEEN 20 AND 40;
Output:

d. To add a column quantity with data type number of size 4.


Code:
ALTER TABLE Supplier
ADD Quantity NUMBER(4);
Output:

e. To display Name, Price, stock of all suppliers whose name starts with 'S'.
Code:
SELECT Name, Price, Stock
FROM Supplier
WHERE Name LIKE 'S%';
Output:
f. To display supplier without duplication
Code:
SELECT DISTINCT Supplier
FROM Supplier;
Output:

g. To insert a row with appropriate values.


Code:
INSERT INTO Supplier (No, Name, Price, Supplier, Stock)
VALUES
(9, 'Graphics Card', 5000, 'NVIDIA', 15);
Output:

h. To increase the stock of all Samsung suppliers.


Code:
UPDATE Supplier
SET Stock = Stock + 5
WHERE Supplier = 'Samsung';
Output:
i. To display all supplier details whose supplier is Philips and stock is above 40.
Code:
SELECT *
FROM Supplier
WHERE Supplier = 'Philips' AND Stock > 40;
Output:

No row is Displayed because in question c we deleted the rows with Stock between 20 and
40.
j. To display name, supplier, stock for name as keyboard or mouse.
Code:
SELECT Name, Supplier, Stock
FROM Supplier
WHERE Name IN ('Keyboard', 'Mouse');
Output:

k. To display names which have ‘oa’ or ‘ou’ in them.


Code:
SELECT Name
FROM Supplier
WHERE Name LIKE '%oa%' OR Name LIKE '%ou%';
Output:
l. List name ,price in descending order by name.
Code:
SELECT Name, Price
FROM Supplier
ORDER BY Name DESC;
Output:

Create the above mentioned tables and insert the records to solve the following queries.
Code:

You might also like