Open MS SQL Server and write a query to achieve the instruction below
1.Create a database named db_ABCCORP
2. Create table named 'tblProducts' with the following columns
+pid
+category
+pname
+quantity
+price
3.Write a query that will add 10 records to the table. The categories should either Appliance, Gadget and Furniture. Price must only range from 1,000 to 5,000.
4. Select all columns where price is below 5,000
5. Select all distinct category
6. Select all columns where category is Gadget and Price is below 6000
7. Select all columns where category is not Furniture
8. Change the price of the product whose id is 7
9. Arrange all the records in descending order of the price
10. Select all columns except quantity
Midterm Notes and Coverage
(1) DBMS.docx (click the link to download)
Coverage:
- Overview of DBMS
- Database Model
- RDBMS Concepts
- ..until Composite Keys
(2) SQL.pdf (click the link to download)
Coverage:
- SELECT
- INSERT INTO
- CREATE TABLE
- CREATE DATABASE
- WHERE CLAUSE
- RELATIONAL OPERATOR
- ORDER BY
- GROUP BY
create database db_ABCCORP
ReplyDeleteuse db_ABCCORP
create table tblProducts
(
PersonID varchar (100) primary key,
Category varchar (100),
PersonName varchar (100),
Quantity int,
Price int
)
insert into tblProducts values ('01','Gadget','Paul',2,1120)
insert into tblProducts values ('02','Furniture','Matt',1,1160)
insert into tblProducts values ('03','Appliance','Perce',3,2120)
insert into tblProducts values ('04','Gadget','Loki',2,3020)
insert into tblProducts values ('05','Furniture','John',1,4020)
insert into tblProducts values ('06','Appliance','Merc',2,3120)
insert into tblProducts values ('07','Gadget','Phyl',4,1120)
insert into tblProducts values ('08','Furniture','Ed',1,2120)
insert into tblProducts values ('09','Appliance','Drake',3,3120)
insert into tblProducts values ('10','Gadget','Pete',2,4120)
select * from tblProducts where price <5000
select distinct category from tblProducts
select * from tblProducts where category='Gadget' and price<6000
select * from tblProducts where category != 'Furniture'
update tblProducts set price=5000 where PersonID='07'
select * from tblProducts order by price desc;
select * from tblProducts except quantity