Question

For each question, write a SQL query and save it as a "sql" file using a different query 2, etc. Please execute these queries sequentially name, for example, query1.query product id (PK) category brand production_cost (Not Null) (FK) name (Not Null) Toothpaste Supermarket 1 Multivitami Supermarket 2 Shampoo acket T-shirt Supplements Supermarket Department4 Department5 Pharmacy Pharmacy 6 6 10 Query1. Update the Product table by adding another column as shown above ucts (name) with uery2. Retrieve all the uction cost al to one dollar 3. List the name(s) of all products under the brand Equate Query4. List the name(s) of all brands with products that have a production cost less than 4 dollars Query5. Retrieve all the pro Query6. List the name(s) of the brands that make more than one product ducts (along with sellin sold in Wal s. ordered by selling price uery7. List the name(s) and selling price of the product(s) which brand is under the "exclve" license Query8. List the name(s) of the brand(s) which make at least one "Supermarket" product and which contract year is after 2012 Query9. List the name(s) of the vendor which has selling price lower than 10 dollars for at least one product in arket cate uery10. List the name(s) of the ucts and the highest selling price for each product Query11. Group and count the products by their category for which their brand is specifically under the "non-exclusive" license uery12. List the names of the products and ordered them by their brand's contract year Query13 Create the following table and name its Category: category id (PK) name (Not Null) Supermarket Pharmacy Update the "category" columns for both Vendor and Product table, alter them into a foreign key that references the Category table

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

This demonstration is using SQL Server 2014.All the tables are created in SQL Server Management Studio (SSMS).

Database :

SQL Query for database creation :

create database ABCCompany;

/*using database ABCCompany */
use ABCCompany;

Table Name : Category

SQL Query :

/*Table : categoey*/
create table Category (
category_id int primary key ,
name varchar(20) not null);

/*Insert into category table*/
insert into Category values (1,'Supermarket');
insert into Category values (2,'Department');
insert into Category values (3,'Pharmacy');

/*retriving data from category table*/
select * from Category;

Screen in SSMS :

Table Name : Vendor

SQL Query :

/*Table Name : Vendor*/
create table vendor(
vendor_id int primary key ,
name varchar(20) not null,
category_id int not null,
address varchar(20) not null,
email varchar(50) not null unique ,
phone_number varchar(50) not null unique,
foreign key (category_id) references category(category_id) );

/*Inserting records into vendor*/
Insert into vendor values (1,'Walmart',1,'9191 W Flagler St','[email protected]','(786)801-5704');
Insert into vendor values (2,'Target',1,'10101 W Flagler St','[email protected]','(305)894-2938');
Insert into vendor values (3,'CVS',3,'1549 SW 107th Ave','[email protected]','(305)220-0147');
Insert into vendor values (4,'Macys',2,'1205 NW 107th Ave','[email protected]','(305)594-6300');
Insert into vendor values (5,'JCPenney',2,'1603 NW 107th Ave','[email protected]','(305)447-1786');
Insert into vendor values (6,'Walgreens',3,'10700 W Flagler St','[email protected]','(305)424-1140');

/*Retriving records from vendor*/
select * from vendor;

Screen in SSMS :

Table Name : Brand

SQL Query :

/*Table : Brand*/
create table Brand(brand_id int primary key ,
name varchar(30) not null,
license varchar(20) not null,
contact_phone varchar(30) not null unique,
contact_email varchar(30) not null,
contract_year int not null)

/*Inserting records into Brand*/
insert into Brand values (1,'Great Value','exclusive','(786)801-1234','[email protected]',2008);
insert into Brand values (2,'Equate','non-exclusive','(360)516-9897','[email protected]',2010);
insert into Brand values (3,'Radiance','non-exclusive','(776)636-9641','[email protected]',2010);
insert into Brand values (4,'Alfani','exclusive','(953)474-8995','[email protected]',2015);
insert into Brand values (5,'Worthington','non-exclusive','(955)812-7462','[email protected]',2016);
insert into Brand values (6,'Botanics','non-exclusive','(305)315-3700','[email protected]',2008);
insert into Brand values (7,'Gold Emblem','non-exclusive','(265)213-7132','[email protected]',2005);

/*Retriving data from Brand table*/
select * from brand;

Screen in SSMS :

Table Name : Product

SQL Query :

/*Table Product*/
create table product(
product_id int primary key ,
name varchar(20) not null,
category_id int not null,
brand int not null,
foreign key (category_id) references category(category_id),
foreign key (brand) references brand(brand_id));

/*inserting records into product table*/
insert into product values (1,'Toothpaste',1,1);
insert into product values (2,'Multivitamin',1,2);
insert into product values (3,'Shampoo',1,2);
insert into product values (4,'Jacket',2,4);
insert into product values (5,'T-shirt',2,5,null);
insert into product values (6,'Supplements',3,6);
insert into product values (7,'Aloe',3,6);
insert into product values (8,'Popcord',3,7);

/*retriving records from product*/
select * from product;

Screen in SSMS :

Table Name : Supply

SQL Query :

/*Table : Supply*/
create table supply(
product_id int ,
vendor_id int ,
selling_price varchar(20) not null,
foreign key (product_id) references product(product_id),
foreign key (vendor_id) references vendor(vendor_id));

/*inserting records into product supply*/
insert into supply values(1,1,4);
insert into supply values(2,2,5);
insert into supply values(3,2,12);
insert into supply values(3,1,5);
insert into supply values(4,5,30);
insert into supply values(5,4,12);
insert into supply values(5,5,10);

/*retriving records from supply*/
select * from supply;

Screen in SSMS :

Query 1 :

Altering Table product :

/*Query 1*/
alter table product add production_cost int;

/*updating production cost into product table*/
update product set production_cost=1 where product_id=1;
update product set production_cost=1 where product_id=2;
update product set production_cost=2 where product_id=3;
update product set production_cost=5 where product_id=4;
update product set production_cost=3 where product_id=5;
update product set production_cost=1 where product_id=6;
update product set production_cost=10 where product_id=7;
update product set production_cost=2 where product_id=8;

Screen in SSMS :

Query 2 :

/*Query 2*/
select name as productName from product where production_cost=1;

Screen in SSMS :

Query 3 :

/*Query 3*/
select product.name as productName from product, brand
where product.brand=brand.brand_id and brand.name='Equate' ;

Screen in SSMS :

Query 4 :

/*Query 4*/
select product.name as productName , brand.name as brandName from product, brand
where product.brand=brand.brand_id and product.production_cost < 4 ;

Screen in SSMS :

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.