sql1-2

zad1-2.sql

use TSQL2012;
--*******************************************************************
--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 'Beverages'
    WHEN 2 then 'Produce'
  WHEN 3 then 'Seafood'
    ELSE 'Other'
    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 'Beverages'
    WHEN 2 then 'Produce'
  WHEN 3 then 'Seafood'
    ELSE 'Others'
    END AS categoryname,
  CASE categoryid
    WHEN 1 then 'Campaing Products'
  WHEN 2 then 'Campaing Products'
  WHEN 3 then 'Campaing Products'
    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 c.custid = o.custid;


--*******************************************************************
--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 o.orderid = o.orderid
WHERE o.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 e.mgrid = m.empid
WHERE
  m.lastname = 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 e.mgrid = m.empid
ORDER BY m.lastname;


--*******************************************************************
--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 $.
---------------------------------------------------------------------
--!!! unitprace -> money type
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 len(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 O.orderid = OD.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), O.orderdate, 112) AS yearmonthno,
  SUM(OD.qty * OD.unitprice)
FROM Sales.Orders AS O, Sales.OrderDetails as OD
WHERE O.orderid = OD.orderid
GROUP BY CONVERT(nvarchar(6), O.orderdate, 112);

---------------------------------------------------------------------
-- 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,
  OD.qty*OD.unitprice AS total
FROM Sales.Orders O, Sales.OrderDetails OD
WHERE O.orderid = OD.orderid
ORDER BY (OD.qty*OD.unitprice) desc
offset 0 rows fetch next 10 rows only;

--*******************************************************************
--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 p.productid, -- productname
FROM
(
  SELECT OD.productid,  (OD.qty*OD.unitprice) AS total
  FROM Sales.Orders O, Sales.OrderDetails OD
  WHERE O.orderid = OD.orderid
) as p
WHERE total > 100;
---------------------------------------------------------------------
-- 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 as C
LEFT JOIN Sales.Orders as O
  ON C.custid = O.custid
WHERE O.custid IS NULL
--*******************************************************************
--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 (productname, color)
  values ('wheel', 'black')

-- Write a SELECT statement to retrieve the orderid column from the Sales.Orders table and the following calculated columns:
-- totalsalesamount (based on the qty and unitprice columns in the Sales.OrderDetails table)
-- salespctoftotal (percentage of the total sales amount for each order divided by the total sales amount for all orders in specific period)

-- Filter the results to include only orders placed in May 2008.