python.01/ASSINGMENT01.sql at main · Hammad360-sys/python.01 · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
-- FIRST ASSINGMENT

-- Question 1 — SELECT & WHERE??

SELECT
	first_name,
	last_name,
	city,
	phone
from
	sales.customers
where
	state = 'CA'
AND
	phone IS NOT NULL;
--COMPLETE


-- 2nd QUESTION;

SELECT
	product_id,
	product_name,
	list_price,
	model_year
FROM
	production.products
ORDER BY
	model_year DESC, list_price ASC;
---COMPLETE


-- QUESTION:3

SELECT TOP 5
	product_name,
	list_price
FROM
	production.products
ORDER BY list_price DESC;

SELECT TOP 5 PERCENT *
FROM production.products
ORDER BY list_price ASC;
---COMPLETE

-- QUESTION; 4

SELECT *
FROM PRODUCTION.products
ORDER BY list_price DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

-----part 2
SELECT *
FROM production.products
ORDER BY list_price DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;


----part 3
SELECT *
FROM PRODUCTION.products
ORDER BY list_price DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
---COMPLETE

--QUESTION:5

SELECT
	distinct state
FROM
	sales.customers
ORDER BY
	state ASC;

---------section:b
select
	distinct state,city
from
	sales.customers
ORDER BY
	state ASC, city ASC;

---section: c
select
	distinct model_year
from
	production.products
ORDER BY
	model_year;

------section:d
SELECT
	COUNT
		(DISTINCT model_year) AS unique_model_years
FROM
	production.products;
---COMPLETE

---QUESTION:6

SELECT
	product_id,
	product_name,
	brand_id,
	category_id,
	list_price
FROM
	production.products
WHERE
	list_price BETWEEN 500 AND 1500
  AND (model_year = 2019 OR model_year = 2020)
ORDER BY
	list_price ASC;
AND
	model_year IN (2019, 2020);

---------COMPLETE--------