zadanie 1 - sql server 2012 - adventure works

zad1-rozw.sql

--*******************************************************************
--ZADANIE 1
--*******************************************************************

-- Create simple database for Library (5 or six tables)
-- -add tables, keys, constraints (default, unique, etc)
-- define correct data types
-- define 3 any views (must be useful and logical)
--

CREATE TABLE Books
(
  BookID int IDENTITY (1,1) NOT NULL
    CONSTRAINT PK_Books_BookID PRIMARY KEY CLUSTERED (BookID),
  BookISBN int NOT NULL
    CONSTRAINT AK_BookISBN UNIQUE(BookISBN),
  Title nvarchar(6) NOT NULL,
  AuthorID int NOT NULL,
  YearPublished int NOT NULL,
  Description nvarchar(200)
);

CREATE TABLE BookReviews
(
    BookReviewsID int IDENTITY (1, 1) NOT NULL
      CONSTRAINT PK_BookReviews_BookReviewsID PRIMARY KEY CLUSTERED (BookReviewsID),
    BookID int NOT NULL,
    ReviewerName Name NOT NULL,
    ReviewDate datetime NOT NULL
      CONSTRAINT DF_BooksReviews_ReviewDate DEFAULT (GETDATE()),
    EmailAddress nvarchar(50) NOT NULL,
    Rating int NOT NULL
      CONSTRAINT CK_BookReviews_Rating CHECK (Rating BETWEEN 1 AND 5),
    Comments nvarchar(3850),
    ModifiedDate datetime NOT NULL
      CONSTRAINT DF_BookReviews_ModifiedDate DEFAULT (GETDATE()),
    CONSTRAINT FK_BookReviews_Book_BookID
      FOREIGN KEY ( BookID ) REFERENCES Books( BookID )
);

CREATE TABLE Author
(
  AuthorID int IDENTITY (1,1) NOT NULL
    CONSTRAINT PK_Author_AuthorID PRIMARY KEY CLUSTERED (AuthorID)
  Firstname nvarchar(60) NOT NULL,
  Lastname nvarchar(60) NOT NULL
);

CREATE TABLE Lenders
(
  LenderID int IDENTITY (1,1) NOT NULL
    CONSTRAINT PK_Lenders_LenderID PRIMARY KEY CLUSTERED (LenderID)
  Firstname nvarchar(60) NOT NULL,
  Lastname nvarchar(60) NOT NULL,
  AddressLine1 nvarchar(60) NOT NULL,
  AddressLine2 nvarchar(60) NULL,
  City nvarchar(30) NOT NULL,
  StateProvinceID int NOT NULL,
  PostalCode nvarchar(15) NOT NULL,
);

CREATE TABLE Lend
(
  LendID int IDENTITY (1,1) NOT NULL
    CONSTRAINT PK_Lend_LendID PRIMARY KEY CLUSTERED (LendID)
  LendDate datetime NOT NULL
    CONSTRAINT DF_Lend_LendDate DEFAULT (GETDATE())
  LendBookID int NOT NULL,
  CONSTRAINT FK_LendBook_Book_BookID
      FOREIGN KEY ( LendBookID ) REFERENCES Books( BookID )

);

CREATE VIEW AS
  ???
CREATE VIEW AS
  ???
CREATE VIEW AS
  ???

-- CREATE FULLTEXT CATALOG LibraryFullTextCatalog AS DEFAULT;
-- CREATE FULLTEXT INDEX ON Book(Description) KEY INDEX PK_Book_BookID;

--*******************************************************************
--ZADANIE 2
--*******************************************************************
---------------------------------------------------------------------
-- Write a SELECT statement to return the contactname, contacttitle, and companyname columns from the Sales.Customers table. Assign these columns with the aliases Name, Title, and Company Name, respectively, in order to return more human-friendly column titles for reporting purposes.
---------------------------------------------------------------------
  select contactname as 'Name', contacttitle as 'Title', companyname as 'Company Name'
  from Sales.Customers;

---------------------------------------------------------------------
-- Write a SELECT statement to display the categoryid and productname columns from the Production.Products table.
---------------------------------------------------------------------
  select categoryid, productname
  from Production.Products;
---------------------------------------------------------------------
-- Enhance the SELECT statement in upper task by adding a CASE expression that generates a result column named categoryname. The new column should hold the translation of the category ID to its respective category name based on the mapping table supplied earlier. Use the value “Other” for any category IDs not found in the mapping table.
---------------------------------------------------------------------
  select categoryid, productname
  case categoryid
    when 1 then 'cos'
    when 2 then 'cos2'
    else 'Others'
    END AS categoryname
  from Production.Products;
---------------------------------------------------------------------
-- Modify the SELECT statement in task 2 by adding a new column named iscampaign. This column will show the description “Campaign Products” for the categories Beverages, Produce, and Seafood and the description “Non-Campaign Products” for all other categories.
---------------------------------------------------------------------
  select categoryid, productname
  case categoryid
    when 1 then 'cos'
    when 2 then 'cos2'
    else 'Others'
    END AS categoryname
  case categoryid
    when 1 then 'cos'
    when 2 then 'cos2'
    else 'Non-Campaign Products'
    END AS iscampaign
  from Production.Products;

--*******************************************************************
--ZADANIE 3
--*******************************************************************

---------------------------------------------------------------------
-- In order to better understand the needed tasks, you will first write a SELECT statement against the HR.Employees table showing the empid, lastname, firstname, title, and mgrid columns.
---------------------------------------------------------------------
  select empid, lastname, firstname, title, mgrid
  from HR.Employees;
---------------------------------------------------------------------
-- Copy the SELECT statement from upper task and modify it to include additional columns for the manager information (lastname, firstname) using a self-join. Assign the aliases mgrlastname and mgrfirstname, respectively, to distinguish the manager names from the employee names.
---------------------------------------------------------------------
  select e1.empid, e1.lastname, e1.firstname, e1.title, e1.mgrid, e2.firstname, e2.lastname
  from HR.Employees as e1;
    inner join HR.Employees as e2
    on e1.empid = e2.empid

---------------------------------------------------------------------
-- Write a SELECT statement to retrieve the custid and contactname columns from the Sales.Customers table and the orderid column from the Sales.Orders table. The statement should retrieve all rows from the Sales.Customers table.
-- OBSERVE NULL VALUES!!!
---------------------------------------------------------------------
  select c.custid, c.contactname, o.orderid
  from Sales.Customers as c
  left join Sales.Orders as o on Sales.Orders.orderid = Sales.Customers.orderid;


--*******************************************************************
--ZADANIE 4
--*******************************************************************

---------------------------------------------------------------------
-- Write a SELECT statement that will return the custid, companyname, contactname, address, city, country, and phone columns from the Sales.Customers table. Filter the results to include only customers from the countries Brazil, UK, and USA.

select custid, companyname, contactname, address, city, country, phone
from Sales.Customers
where country = 'Brazil' or country = 'UK' or country = 'USA';

---------------------------------------------------------------------

---------------------------------------------------------------------
-- Write a SELECT statement that will return the custid, companyname, contactname, address, city, country, and phone columns from the Sales.Customers table. Filter the results to include only the customers with a contact name starting with the letter A.

select custid, companyname, contactname, address, city, country, phone
from Sales.Customers
where contactname like 'A%';

---------------------------------------------------------------------

---------------------------------------------------------------------
-- Write a T-SQL statement to retrieve customers from the Sales.Customers table that do not have matching orders in the Sales.Orders table. Matching customers with orders is based on a comparison between the customer’s custid value and the order’s custid value. Retrieve the custid and companyname columns from the Sales.Customers table.
---------------------------------------------------------------------
select c.custid, c.companyname
from Sales.Customers as c
left join Sales.Orders as o on Sales.Orders.orderid = Sales.Customers.orderid
where Sales.Customers.orderid is null

---------------------------------------------------------------------
-- Write a SELECT statement to retrieve the custid, orderid, and orderdate columns from the Sales.Orders table. Order the rows by orderdate and orderid. Retrieve the first 20 rows.
---------------------------------------------------------------------
select top (20) custid, orderid, orderdate
from Sales.Orders
order by orderdate, orderid;

---------------------------------------------------------------------
-- Copy the SELECT statement in upper task and modify the OFFSET-FETCH clause to skip the first 20 rows and fetch the next 20 rows.
---------------------------------------------------------------------
select custid, orderid, orderdate
from Sales.Orders
order by orderdate, orderid
offset 20 rows fetch next 20 rows only;

---------------------------------------------------------------------
-- Apply the needed changes to the SELECT statement so that it will run without an error. Test the changes by executing the T-SQL statement.
---------------------------------------------------------------------

SELECT
  e.empid, e.lastname, e.firstname, e.title, e.mgrid,
  m.lastname AS mgrlastname, m.firstname AS mgrfirstname
FROM HR.Employees AS e
INNER JOIN HR.Employees AS m ON HR.Employees.mgrid = HR.Employees.empid
WHERE
  mgrlastname = N'Buck';
/* */


---------------------------------------------------------------------
-- Copy the existing T-SQL statement and modify it so that the result will return all employees and be ordered by the manager’s first name. Try first to use the source column name, and then try to use the alias column name.
---------------------------------------------------------------------

SELECT
  e.empid, e.lastname, e.firstname, e.title, e.mgrid,
  m.lastname AS mgrlastname, m.firstname AS mgrfirstname
FROM HR.Employees AS e
INNER JOIN HR.Employees AS m ON HR.Employees.mgrid = HR.Employees.empid
ORDER BY HR.Employees.mgrlastname;


--*******************************************************************
--ZADANIE 5
--*******************************************************************

---------------------------------------------------------------------
-- Write a SELECT statement to retrieve distinct values for the custid column from the Sales.Orders table. Filter the results to include only orders placed in February 2008.
---------------------------------------------------------------------
select distinct custid
FROM Sales.Orders
where orderdate between '20080229' and '20080201';


-- This one can better utilize indexing than the first solution.


---------------------------------------------------------------------
-- Write a SELECT statement to retrieve the contactname column from the Sales.Customers table and replace the comma in the contact name with an empty string. Based on this column, add a calculated column named firstname, which should consist of all the characters after the comma.
---------------------------------------------------------------------
select Replace(ContactName, "," , ""), PARSENAME(REPLACE(contactname, ',', '.'), 1) as firstname
from Sales.Customers
---------------------------------------------------------------------
-- Write a SELECT statement to retrieve the contactname column from the Sales.Customers table. Add a calculated column, which should count the number of occurrences of the character ‘a’ inside the contact name. (Hint: Use the string functions REPLACE and LEN.) Order the result from rows with the highest occurrences to lowest.
---------------------------------------------------------------------
select contactname, LEN(contactname)-LEN(REPLACE(contactname,'a','')) as NumberOfA
from Sales.Customers
order by NumberOfA desc;
---------------------------------------------------------------------
-- Write a SELECT statement against the Production.Products table to retrieve a calculated column named productdesc. The calculated column should be based on the columns productname and unitprice and look like this:
--  The unit price for the Product HHYDP is 18.00 $.
---------------------------------------------------------------------
select ('The unit price for the Product '+productname+' is '+unitprice) as productdesc
from Production.Products;

--------------------------------------------------------------------
-- Write a SELECT statement against the Sales.Customers table and retrieve the custid and contactname columns. Add a calculated column named segmentgroup using the logic function CHOOSE with four possible descriptions (“Group One”, “Group Two”, “Group Three”, “Group Four”). Use the modulo operator on the column custid. (Use the expression custid % 4 + 1 to determine the target group.)
---------------------------------------------------------------------
select custid, contactname, choose (custid%4+1,'Group One', 'Group Two', 'Group Three', 'Group Four')as segmentgroup
from Sales.Customers;
---------------------------------------------------------------------
-- Write a SELECT statement to retrieve the contactname, city, and region columns from the Sales.Customers table. Return only rows that do not have two characters in the region column, including those with an inapplicable region (where the region is NULL).
---------------------------------------------------------------------
select contactname, city, region
from Sales.Customers;
where lenght(region)<>2 or region is null
--*******************************************************************
--ZADANIE 6
--*******************************************************************
--------------------------------------------------------------------
-- Write a SELECT statement to retrieve the orderid column from the Sales.Orders table and the total sales amount per orderid. (Hint: Multiply the qty and unitprice columns from the Sales.OrderDetails table.) Use the alias salesmount for the calculated column. Sort the result by the total sales amount in descending order.
---------------------------------------------------------------------
select O.orderid, OD.qty*OD.unitprice as salesamount
from Sales.Orders as O, Sales.OrderDetails as OD
where Sales.Orders.orderid = Sales.OrderDetails.orderid
order by salesamount desc;
---------------------------------------------------------------------
-- Write a select statement to retrieve the total sales amount for each month. The SELECT clause should include a calculated column named yearmonthno (YYYYMM notation) based on the orderdate column in the Sales.Orders table and a total sales amount (multiply the qty and unitprice columns from the Sales.OrderDetails table). Order the result by the yearmonthno calculated column.
---------------------------------------------------------------------
select CONVERT(nvarchar(6), Sales.OrderDetails.orderdate, 112) as yearmonthno,
!!!!
from Sales.Orders
where Sales.Orders.orderid = Sales.OrderDetails.orderid

---------------------------------------------------------------------
-- Write a SELECT statement to retrieve the top 10 customers by total sales amount that spent more than $10,000 in terms of sales amount. Display the custid column from the Orders table and a calculated column that contains the total sales amount based on the qty and unitprice columns from the Sales.OrderDetails table. Use the alias totalsalesamount for the calculated column.
---------------------------------------------------------------------
select O.custid, top (10) OD.qty*OD.unitprice as total
where Sales.Orders.custid = Sales.OrderDetails.custid
from Sales.Orders O, Sales.OrderDetails OD

--*******************************************************************
--ZADANIE 7
--*******************************************************************

---------------------------------------------------------------------
-- Write a SELECT statement to return the orderid, orderdate, empid, and custid columns from the Sales.Orders table. Filter the results to include only orders where the date order equals the last order date. (Hint: Use the query in task 1 as a self-contained subquery.)
---------------------------------------------------------------------
select orderid, orderdate, empid, custid
from Sales.Orders
where orderdate = (
 select top (1) orderdate
 from Sales.orders
 order by orderdate
 -- offset 0 rows fetch next 1 rows only
)
---------------------------------------------------------------------
-- Write a SELECT statement to retrieve the productid and productname columns from the Production.Products table. Filter the results to include only products that were sold in high quantities (more than 100 products) for a specific order line.
---------------------------------------------------------------------
select productid, productname !!!!
from Production.Products
where
---------------------------------------------------------------------
-- Write a SELECT statement to retrieve the custid and contactname columns from the Sales.Customers table. Filter the results to include only those customers that do not have any placed orders.
---------------------------------------------------------------------
select custid, ContactName !!!!
from Sales.Customers
left join Sales.Orders
  on Sales.Customers.custid
--*******************************************************************
--ZADANIE 8
--*******************************************************************

-- write scripts
-- delete rows from production.product where product color = null and category or subcategory contains bike

delete
  from Production.Products
  where color is null
    and ( productsubcategory = 'bike' or productcategory = 'bike' )
;

-- update name of product: add RED of the end of product name where product color is red

update Production.Products
  set name = name + 'RED'
  where color = 'red'

-- insert new product to table of products.

insert
  into Production.Products (name, color)
  values ('wheel', 'black')

Need answers for below
1.Get total count of orders placed

2.   Get total count of orders placed by year
   

3.   Get total due amount by shipdate
   

4.   List all orders that sold more than 2 products
   

5.   List all orders that have total due greater than 20,000
   

6.   Get employee name along with total sales in Descending order