use workbench please
Task 1: Create a database and name it practical2DB. (2
marks)
Task 2: Create all the four tables according to the relational
scheme in Figure 1. (8 marks)
Task 3: Insert 5 records to into each table. (5 marks)
Task 4: Write a query to list alphabetically ordered names,
addresses, and IDs of all the customers whose postcode is in
(40150, 40400, 47500).
(10 marks)
Task 5: Delete 1 record from Products table using their primary
keys. (5 marks)
Task 6: Add a column by the name CustPhone with datatype INTEGER.
(5 marks)
Task 7: Delete the column Customer address from the table Customer.
(5 marks)
Task 8: Increase by 2 the value of the attribute QuantityOrdered of
one of the records in the table "LineItems". (10 marks)
Task 9: Create a report to list all customer ID, order numbers,
product id, product description, product price, quantity, and order
total price for all of the available orders records. (13
marks)
Task 10: Write a query to list the total number of products,
minimum price, maximum price, and average price of the products.
(10 marks)
Task 11: Select the top 2 products that have been ordered more. (10
marks)
Task 12: Create a View with name "outOfStock" in which a list of
all products (if there is any) that are out of stock will be
reported. Test this View and prove it is working properly. (12
marks)
Task 13: Delete the View by using proper command. (5 marks)
TASK 1:
CREATE DATABASE practical2DB
TASK 2:
CREATE TABLE Customer
(
CustID INT PRIMARY KEY NOT NULL,
CustName VARCHAR(25)NOT NULL,
Custaddress VARCHAR(45) NOT NULL,
CustPostCode CHAR(8)NOT NULL
)
CREATE TABLE Products
(
ProductID INT PRIMARY KEY NOT NULL,
Description VARCHAR(25)NOT NULL,
StorageAddress VARCHAR(25) ,
StoragePostCode VARCHAR(25),
Reorderedquantity INT(4) NOT NULL,
QuantityInStock INT(4) NOT NULL,
Unitprice DECIMAL,
Total_quantitySold INT
)
CREATE TABLE Orders
(
OrderID INT(4) PRIMARY KEY NOT NULL,
CustID INT(4) NOT NULL,
OrderDate DATE NOT NULL,
PromiseDate DATE NOT NULL
)
CREATE TABLE LineItems
(
ProductID INT(4) NOT NULL,
OrderNo INT(4) NOT NULL,
QuantityOrdered INT(4) NOT NULL
)
TASK 3:
INSERT INTO Customer VALUES(45678, "Vijay Kumar","108/87 road",'APY85120')
INSERT INTO Products VALUES(45678, "Laptop","108/87 road",'APY85120',5,5,25000,15)
INSERT INTO Orders VALUES(6758, 3478,12-03-2018,15-04-2019)
INSERT INTO LineItems VALUES(6758, 3489,7)
TASK 4:
SELECT CustName,CustID ,Custaddress
FROM Customer
WHERE CustPostCode IN (40150, 40400, 47500)
ORDER BY CustName
TASK 5:
DELETE FROM Products
WHERE ProductID=4
TASK 6:
ALTER TABLE Customer
ADD CustPhone INT
TASK 7:
ALTER TABLE Customer
DROP COLUMN Custaddress
TASK 8:
UPDATE TABLE LineItems
SET QuantityOrdered =QuantityOrdered*2
use workbench please Task 1: Create a database and name it practical2DB. (2 marks) Task 2:...
Question for SQL in MS SQL Server Management Studio (Please don't use where clause for Joins use from clause) Need to Create a Query because Northwind Traders wants to make sure that all orders are shipped within 10 days. Display any orders that were not shipped within 10 days. Include the OrderID, the OrderDate, and the number of days it took before the order was shipped. Customers PK Customer D CompanyName Contact Name Contact Title Address City State OrRegion PostalCode...
Questions are based on the Northwind database. a. A data dictionary (Excel file) that describes each of the tables. (Attached in question) b. Write good, clean SQL that answers the following questions. c. Separate your queries as I have done in the following example. End each query with a semicolon (;) and insert the word "go" between each query. Queries can span multiple lines if required. Select CustomerID from Customers; go Select Count(*) from Employees; go Select max(productID) from Products; 18. Produce...
Question for SQL in MS SQL Server Management Studio (Please don't use where clause for Joins use from clause) Need to Create a Query to display products purchased by customers in Germany that were not purchased by customers in the US. Customers PK Customer D CompanyName Contact Name Contact Title Address City State OrRegion PostalCode Country Phone char(5) varchar(40) varchar(30) varchar(30) varchar(60) varchar(15) varchar(15) varchar(10) varchar(15) varchar(24) varchar(24) Order Details Orders PK OrderID int FK CustomerlD char(5) FK Employeeld int...
Overview: Database management plays an integral role in nearly every area of business. Databases house customer, accounting, and employee data, and these different data sets must all be efficiently managed in order to make the data accessible. Companies rely on database engineers to ensure that their records are accurate, updated, and tracked in real time. This course covers structured query language (SQL) and how it can be used to manage database schemas, manipulate data, and analyze data. For your final...
Question for SQL in MS SQL Server Management Studio (Please don't use where clause for Joins use from clause) Need to Create a Query because Northwind Traders has decided to increase their retail prices. Write a query that shows the product name, the current price, and the new price. Make sure the new price is listed to 2 decimal points. Follow below guidelines on new retail price: a. If the category is beverages or dairy products, increase the price by...
The Northwind database created by Microsoft contains the sales data for a fictitious company called Northwind Traders, which imports and exports specialty foods from around the world. Here is the schema of the database: Products (ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued); Suppliers (SupplierID, CompanyName, ContactName , ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax, HomePage); Categories (CategoryID, CategoryName, Description, Picture); Orders (OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName,ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry); Order_details (ID, OrderID,...
can someone solve these quick please and thank you! sql chapter 4 Query #1: List the company name, contact name, contact title and the phone number for customers who HAVE NOT put in an order. Use an outer join. Query #2: Create a listing displaying the employee first name, last name and the full name (First name space Last Name) of the person they report to (Supervisor). This is a self-join. If an employee does not report to anyone then...
Tables: Create table Item( ItemId char(5) constraint itmid_unique primary key, Decription varchar2(30), Unitcost number(7,2)); Create table Customer( custID char(5) constraint cid.unique primary key, custName varchar2(20), address varchar2(50)); Create table Orderdata( orderID char(5) constraint oid_uniq primary key, orderdate date, shipdate date, ItemId char(5) references Item.ItemId, No_of_items number(4), Unitcost number(7,2), Order_total number(7,2), custID char(5) references customer.custID); Insert Into Item values(‘A123’,’Pencil’,2.5); Insert Into Item values(‘B123’,’Pen’,15); Insert Into...
SQL I have a database CREATE TABLE vendor ( vid CHAR(2) NOT NULL, vname VARCHAR(25) NOT NULL, PRIMARY KEY (vid) ); CREATE TABLE category ( catid CHAR(2) NOT NULL, catname VARCHAR(25) NOT NULL, PRIMARY KEY (catid) ); CREATE TABLE product ( pid CHAR(3) NOT NULL, pname VARCHAR(25) NOT NULL, price NUMERIC (7,2) NOT NULL, vid CHAR(2) NOT NULL, categoryid CHAR(2) NOT NULL, PRIMARY KEY (pid)); CREATE TABLE region ( rid CHAR NOT NULL, rname VARCHAR(25) NOT NULL, PRIMARY KEY (rid)...
rider_student Column Data Type Description student_id integer the primary key first_name varchar(25) student first name last_name varchar(25) student last name major_id integer the ID of the student's major; a foreign key for the major_id in the rider_major table rider_major Column Data Type Description major_id integer the primary key major_name varchar(50) student first name major_description varchar(100) student last name Use the Tables above to answer the questions. Questions: 1. Write a SQL statement to add a student record to the rider_student...