SQL-Assignement 4: This assignment follows last three SQL-assignments. Below are
ID: 3577493 • Letter: S
Question
SQL-Assignement 4:
This assignment follows last three SQL-assignments.
Below are more SQL queries. Please first read chapter 7 and each following part carefully then write and execute it in MS SQL server management studio.
Once you get to successful result, take a screenshot of each query and respective successful message. Copy screenshots into a word document and submit it in D2L. PLEASE COPY THE ENTIRE SCREEN IN YOUR SCREEN SHOT. I want to see your entire environment in your screen shot that includes the query and result.
Outer Join:
select faculty.facultyID, faculty.facfirstname, dependants.FacultyID,dependants.DepFirstname
from faculty Full OUTER join dependants on
faculty.FacultyId = dependants.FacultyID
-------------------------------------------------------------------
Left outer join:
select faculty.facultyID, faculty.facfirstname, dependants.FacultyID,dependants.DepFirstname
from faculty left OUTER join dependants on
faculty.FacultyId = dependants.FacultyID
-------------------------------------------------------------------
Join:
select faculty.facultyID, faculty.facfirstname, dependants.FacultyID,dependants.DepFirstname
from faculty join dependants on
faculty.FacultyId = dependants.FacultyID
-------------------------------------------------------------------
Where Clause:
select faculty.facultyID, faculty.facfirstname, dependants.FacultyID,dependants.DepFirstname
from faculty,dependants
where faculty.FacultyId = dependants.FacultyID;
-------------------------------------------------------------------
Inner Join:
select faculty.facultyID, faculty.facfirstname, dependants.FacultyID,dependants.DepFirstname
from faculty inner join dependants on
faculty.FacultyId = dependants.FacultyID;
-------------------------------------------------------------------
Union:
SELECT customerId AS Customer_ID, name as Customers_name from Customers
UNION
SELECT orderID As Order_ID, orderdate as order_date from orders;
Conversion failed when converting date and/or time from character string.
-------------------------------------------------------------------
CREATE TABLE CUSTOMERS(
CustomerID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (CustomerID)
);
INSERT INTO CUSTOMERS (CustomerID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'John', 32, 'Michael', 2000.00 );
INSERT INTO CUSTOMERS (CustomerID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Kian', 25, 'Jay', 1500.00 );
INSERT INTO CUSTOMERS (CustomerID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Sam', 23, 'Kota', 2000.00 );
INSERT INTO CUSTOMERS (CustomerID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Merry', 25, 'Mani', 6500.00 );
INSERT INTO CUSTOMERS (CustomerID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Harold', 27, 'Jefferson', 8500.00 );
INSERT INTO CUSTOMERS (CustomerID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Bill', 22, 'pearson', 4500.00 );
CREATE TABLE ORDERS
(OrderId INT IDENTITY(1,1) PRIMARY KEY,
CustomerID INT Not NULL,
OrderDate DATETIME);
INSERT INTO orders
(CustomerId,OrderDate)
VALUES(11,'8/1/16');
-------------------------------------------------------------------
Inner Join:
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;
-------------------------------------------------------------------
Left Join:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
-------------------------------------------------------------------
Right Join:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
-------------------------------------------------------------------
Outer Join:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
-------------------------------------------------------------------
Some View examples:
create view CustomerOrder as
select
CUSTOMERS.ID,
Customers.name,
CUSTOMERS.salary,
Orders.Orderdate
from
CUSTOMERS
join ORDERS
on Customers.ID = Orders.CustomerId
where
Customers.age > 18
go
-------------------------------------------------------------------
create view CustomerOrderCount as
select
CustomerID,
COUNT(orderId) as [OrderCount]
from
Orders
group by
customerId
go
-------------------------------------------------------------------
create view customersMaxSalary as
select
Name,
Salary
from
CUSTOMERS
where
Salary in (select MAX(Salary) from Customers)
Select * from customersMaxSalary
-------------------------------------------------------------------
Explanation / Answer
EACH SQL QUERY ARE CORRECT.
WHAT THE PROBLEM?
Outer Join:
select faculty.facultyID, faculty.facfirstname, dependants.FacultyID,dependants.DepFirstname
from faculty Full OUTER join dependants on
faculty.FacultyId = dependants.FacultyID
-------------------------------------------------------------------
Left outer join:
select faculty.facultyID, faculty.facfirstname, dependants.FacultyID,dependants.DepFirstname
from faculty left OUTER join dependants on
faculty.FacultyId = dependants.FacultyID
-------------------------------------------------------------------
Join:
select faculty.facultyID, faculty.facfirstname, dependants.FacultyID,dependants.DepFirstname
from faculty join dependants on
faculty.FacultyId = dependants.FacultyID
-------------------------------------------------------------------
Where Clause:
select faculty.facultyID, faculty.facfirstname, dependants.FacultyID,dependants.DepFirstname
from faculty,dependants
where faculty.FacultyId = dependants.FacultyID;
-------------------------------------------------------------------
Inner Join:
select faculty.facultyID, faculty.facfirstname, dependants.FacultyID,dependants.DepFirstname
from faculty inner join dependants on
faculty.FacultyId = dependants.FacultyID;
-------------------------------------------------------------------
Union:
SELECT customerId AS Customer_ID, name as Customers_name from Customers
UNION
SELECT orderID As Order_ID, orderdate as order_date from orders;
Conversion failed when converting date and/or time from character string.
-------------------------------------------------------------------
CREATE TABLE CUSTOMERS(
CustomerID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (CustomerID)
);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.