1. Find the top 10 highest OrderPrice which derived by calculating the product o
ID: 3731762 • Letter: 1
Question
1. Find the top 10 highest OrderPrice which derived by calculating the product of unitprice and qty. Also, derive the DiscountedOrderPrice by extending the OrderPrice calculation and applying the discount. Finally, find the number of days between orderdate and shippeddate using datediff.
select o.custid, o.orderdate, o.shippeddate, JulyOrders = substring (cast(o.orderdate as char(10)),1,7), od.unitprice, od.qty , od.discount, DaysFromOrderToShip = , OrderPrice = , DiscountredOrderPrice =
from Sales.Orders as o
Sales.OrderDetails as od on od.orderid = o.orderid
where
order by
2.
Find all of the tables in the database in the FullyQualifiedTableName format “SchemaName.TableName”.
Use the concat function to create the FullyQualifiedTableName. Also, Alias (rename) the SchemaName and the TableName separately.
Hint: Use the SCHEMA_NAME function to convert the ID of the schema to the SchemaName.
select SchemaName =
, TableName =
, FullyQualifitedTableName =
From sys.tables;
3.
Find the Total number of columns and the TotalRowStorage for the datatypes in the table and fix the case statement to test for the two datatypes that add 2 bytes of additional overhead (change ‘What datatype 1’ , ‘What datatype 2’ in the case statement).
select TotalNumberOfColumns = count(*)
, TotalRowStorage = sum(case when type_name(system_type_id) in (‘What datatype 1’, ‘What datatype 2’ )
then max_length + 2
else max_length
end)
from sys.columns
where object_id = object_id(N’hr.Employees’);
Complete the function named DoubleTheValueOfTheInput that passes a single integer parameter and calculate the parameter times itself. (ie 5 x 5 =25)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.DoubleTheValueOfTheInput
(
--Add the parameters for the function here
@IntegerNumber int
)
RETURNS int
AS
BEGIN
--Declare the return variable here
DECLARE @Result int
SELECT @Result = -- Add the T-SQL statements to compute the return value here
--Return the result of the function
RETURN @Result
END
GO
4. What is the distinct count of the union of the queries below?
What is the count of the union with duplicates of the queries below/.
In set theory, the difference of sets of these two sets (HR.Employees – sales.Customers) using the queries below?
select country, region, city FROM HR.Employees
select country, region, city FROM sales.Customers as c
Explanation / Answer
--1
select TOP 10 o.custid,
o.orderdate,
o.shippeddate,
substring (cast(o.orderdate as char(10)),1,7) AS JulOrders, /*Im not sure what you are achieving here*/
od.unitprice,
od.qty ,
od.discount,
DaysFromOrderToShip = ,
(od.qty*od.unitprice) AS OrderPrice,
(od.qty*od.unitprice*od.discount) AS DiscountredOrderPrice
from Sales.Orders as o
Sales.OrderDetails as od
on od.orderid = o.orderid
--where --Removing where condition since question doesnt contain any condition*/ Please comment if any where condition is required. So i can modify
order by OrderPrice DESC;
--2
select TABLE_SCHEMA AS SchemaName
,TABLE_NAME AS TableName
,TABLE_SCHEMA+'.'TABLE_NAME AS FullyQualifitedTableName /*+ is concatenate in sql server*/ /* Concat is in oracle*/
From information_schema.tables WHERE table_type = 'BASE TABLE' AND TABLE_SCHEMA='SchemaName'/*Please enter schema name*/;
--3
select count(*) AS TotalNumberOfColumns,
TotalRowStorage = sum(case when type_name(SELECT system_type_id FROM sys.types WHERE system_type_id in (‘What datatype 1’, ‘What datatype 2’ )) then max_length + 2
else max_length
end)
from sys.columns
where object_id = object_id(N’hr.Employees’);
--4
select count(distinct country)
from
(select country, region, city FROM HR.Employees
UNION
select country, region, city FROM sales.Customers as c) as SubQueryAlias
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.