SQL Exercises 1.Create a new table that will be used to track the tblCustomer ad
ID: 3691716 • Letter: S
Question
SQL Exercises
1.Create a new table that will be used to track the tblCustomer address and contact information. The attributes associated with the table: customer_id, address_type street address, city, state, zipcode, phone number, customer name and e-mail address. Create a new table the attributes listed above. The primary key is customer_id, and address_type. It has a relationship with the customer table. Provide SQL statement below.
2.Create new table it will be used to identify the Sales associate gets the credit for the customer order. This attributes associated with table: Salesassoc_id, sales assoc_name, and sales location. The primary key is salesassoc_id. The table has a relationship with the order table Provide SQL statement below.
3.Create a query to select all data from the tblcustomer data. Provide the SQL statement below:
4.Create a basic query that will extract the following information from the tblProduct table: ingproductid, strproductname, chkinstock, Ingunitsinstock, ingshippingid. Provide the SQL statement below:
5.You need to find all customer orders placed between January 2011 and June 2011. Provide the SQL statement below:
6.Create a new query to display all orders. Show the ingOrderid, ingCustomerid, strcustomerfirstname, strcustomerlastname and dtdateorderplaced, . Group data by IngOrderid. Provide the SQL statement below:
7.Delete all orders placed before January 2001. Provide the SQL statement below.
Explanation / Answer
Hi below i have given the solution for your reference :)
1.Create a new table that will be used to track the tblCustomer address and contact information. The attributes associated with the table: customer_id, address_type street address, city, state, zipcode, phone number, customer name and e-mail address. Create a new table the attributes listed above. The primary key is customer_id, and address_type. It has a relationship with the customer table. Provide SQL statement below.
CREATE TABLEtblCustomerAddress(
Customer_id INT,
Address_type VARCHAR (100 BYTE),
Street_address VARCHAR (75 BYTE),
City VARCHAR (40 BYTE),
State VARCHAR (3 BYTE),
Zipcode VARCHAR (10 BYTE),
Phone_No VARCHAR (10 BYTE),
Customer_FirstName VARCHAR (40 BYTE),
Customer_LastName VARCHAR (40 BYTE),
Email_Address VARCHAR (100 BYTE),
PRIMARY KEY (Customer_id, Address_type)
);
2.Create new table it will be used to identify the Sales associate gets the credit for the customer order. This attributes associated with table: Salesassoc_id, sales assoc_name, and sales location. The primary key is salesassoc_id. The table has a relationship with the order table Provide SQL statement below.
create table SalesAssociateCredit(Salesassoc_id int PRIMARY KEY,
inorderid,salesassoc_name char(20), sales_location varchar(30));
Alter table SalesAssociateCredit with check add constraint fkconstraint FOREIGN KEY inorderid
references tblorders.inorderid;
3) Create a query to select all data from the tblcustomer data. Provide the SQLstatement below:
Select * from tblcustomer;
4) Create a basic query that will extract the following information from thetblProduct table: ingproductid, strproductname, chkinstock, Ingunitsinstock,ingshippingid.Provide the SQL statement below:
SELECT ingproductid, strproductname, chkinstock, Ingunitsinstock, ingshippingid
FROM tblProduct;
5)You need to find all customer orders placed between January 2011 and June 2011.Provide the SQL statement below:
Select * from tblOrders where dteDateOrderPlaced between ‘01/01/2011’and‘06/30/2011';
6) Create a new query to display all orders. Show the ingOrderid, ingCustomerid,strcustomerfirstname, strcustomerlastname and dtdateorderplaced. Group data byIngOrderid. Provide the SQL statement below:
Select ingOrderid, ingCustomerid, strcustomerfirstname, strcustomerlastname,dtdateorderplaced from tblorders
Group data by ingOrderid, ingCustomerid, strcustomerfirstname,strcustomerlastname, dtdateorderplaced
7) Delete all orders placed before January 2001.Provide the SQL statement below.
Delete tblorder where dteDateOrderPlaced<’01/01/2001’;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.