SQL-Problems/SQLQuery1.sql at main · Ahmad-L23/SQL-Problems · 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
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
select * from(
select count(*) as numberOfVehilces, m.make from VehicleDetails v
inner join Makes m on v.MakeID = m.MakeID
where year between 1950 and 2000
group by make
)R1
where numberOfVehilces > 12000
order by numberOfVehilces desc

select *, cast(numberOfVehilces as float)/ cast(total as float) as prec
from(
select count(*) as numberOfVehilces, m.make, (select COUNT(*) from VehicleDetails)as total from VehicleDetails v
inner join Makes m on v.MakeID = m.MakeID
where year between 1950 and 2000
group by make
)R1
order by numberOfVehilces desc


select m.make, f.FuelTypeName, count(*) as numberOfVehicles
from VehicleDetails inner join Makes m on m.MakeID = VehicleDetails.MakeID
inner join FuelTypes f on f.FuelTypeID = VehicleDetails.FuelTypeID
where (VehicleDetails.Year between 1950 and 2000)
group by m.Make, f.FuelTypeName
order by m.Make


select count(*) as total from(
select distinct Makes.Make, FuelTypes.FuelTypeName from VehicleDetails
inner join FuelTypes on VehicleDetails.FuelTypeID = FuelTypes.FuelTypeID
inner join Makes on VehicleDetails.MakeID = Makes.MakeID
where FuelTypes.FuelTypeName = N'GAS'
)R1


select makes.Make, count(*) as numberOfVehicles from VehicleDetails
inner join Makes on Makes.MakeID = VehicleDetails.MakeID
group by Makes.Make
having COUNT(*) > 20000
order by numberOfVehicles desc


select make from makes where make like 'B%'


select make from makes where make like '%W'


select distinct makes.make, DriveTypes.DriveTypeName
from DriveTypes inner join VehicleDetails on DriveTypes.DriveTypeID = VehicleDetails.DriveTypeID
inner join makes on makes.MakeID = VehicleDetails.MakeID
where DriveTypes.DriveTypeName = 'FWD'


select count(*) as makeWithFWD from
(select distinct makes.make, DriveTypes.DriveTypeName
from DriveTypes inner join VehicleDetails on DriveTypes.DriveTypeID = VehicleDetails.DriveTypeID
inner join makes on makes.MakeID = VehicleDetails.MakeID
where DriveTypes.DriveTypeName = 'FWD'
)R1


select distinct makes.Make, DriveTypes.DriveTypeName, count(*) as total from DriveTypes
inner join VehicleDetails on VehicleDetails.DriveTypeID = DriveTypes.DriveTypeID
inner join Makes on Makes.MakeID = VehicleDetails.MakeID
group by Makes.Make, DriveTypes.DriveTypeName
order by Makes.Make asc, total desc


select distinct makes.Make, DriveTypes.DriveTypeName, count(*) as total from DriveTypes
inner join VehicleDetails on VehicleDetails.DriveTypeID = DriveTypes.DriveTypeID
inner join Makes on Makes.MakeID = VehicleDetails.MakeID
group by Makes.Make, DriveTypes.DriveTypeName
having count(*) > 10000
order by Makes.Make asc, total desc


select * from(
select distinct makes.Make, DriveTypes.DriveTypeName, count(*) as total from DriveTypes
inner join VehicleDetails on VehicleDetails.DriveTypeID = DriveTypes.DriveTypeID
inner join Makes on Makes.MakeID = VehicleDetails.MakeID
group by Makes.Make, DriveTypes.DriveTypeName
)R1
where total > 10000
order by Make asc, total desc


select
(
cast ( (select count(*) as total from VehicleDetails where NumDoors is null) as float)
   /
cast((select count(*) from VehicleDetails as totalVehicle) as float)
)as PercOfNoSpecifiedDoors


select distinct VehicleDetails.MakeID ,makes.make, SubModelName from VehicleDetails
inner join Makes on makes.MakeID = VehicleDetails.MakeID
inner join SubModels on SubModels.SubModelID = VehicleDetails.SubModelID
where SubModels.SubModelName = 'Elite'


select * from VehicleDetails where  Engine_Liter_Display > 3 and NumDoors = 2


select makes.Make, VehicleDetails.* from VehicleDetails
inner join Makes on Makes.MakeID = VehicleDetails.MakeID
where VehicleDetails.Engine like '%OHV%' and VehicleDetails.Engine_Cylinders = 4


select VehicleDetails.*, bodyName from VehicleDetails
inner join Bodies on VehicleDetails.BodyID = Bodies.BodyID
where BodyName = 'Sport Utility' and year > 2020


select bodyName, VehicleDetails.* from VehicleDetails
inner join Bodies on VehicleDetails.BodyID = Bodies.BodyID
where BodyName in('Coupe', 'Hatchback', 'Sedan')


select BodyName, VehicleDetails.* from VehicleDetails
inner join Bodies on Bodies.BodyID = VehicleDetails.BodyID
where BodyName in('Coupe', 'Hatchback', 'Sedan') and year in (2008, 2020, 2021)


select found = 1
where exists( select top 1 * from VehicleDetails where year = 1950)