create database dbstor
create database dbstor
use dbstor
create table tblprice
(item nvarchar(20) ,wholesale money)
/**********************Insert*************************/
--Insert statement
--1)insert values for all columns(features)
insert into tblprice values ('apple',2.2),('orange',1.3),
('banana',1.1),('grape',3.3)
select * from tblprice
go
--2)insert a record(row) that just has values for some columns not for all
-- insert into table name (column1,column3,column6) values (value1,value3, value6)
insert into tblprice (item) values ('Apple')
select * from tblprice
/* Note: If you want to insert value for all columns you don't have to mention the name of the columns otherwise
you must specify the name of columns that you want to insert value for them before values */
insert into tblprice values ('carot',1.51)
select * from tblprice
/**********************Update**********************/
--Update:This command is used to modify information inside of a table that belongs to DML(Data Manipulation Language)
update tblprice
set item='carrot'
where item='carot'
select * from tblprice
go
--Ex: change the price of orange to 1.21
update tblprice -- here we want to set the price of all oranges to 1.21
set wholesale=1.21
where item='orange'
go
select * from tblprice
/*and and between*/
--Example:Find all of information of items that their wholesale is bigger than 1 and less than 2
select * from tblprice
where wholesale>1 and wholesale<2
--or
------------------------------------------------------
/*between*/
------------------------------------------------------
select * from tblprice where wholesale between 1.01 and 1.99 -- between accepts end points as well
--Ex:Find all of information of itemes that their wholesale is bigger than 1.1 and less than 2
select * from tblprice where wholesale>1.1 and wholesale<2
select * from tblprice where wholesale between 1.01 and 1.99
-----------------------------------------------
/*in and or*/
-----------------------------------------------
--If more than one value is acceptable in where condition:
--Ex: find all information of items that their wholesale is 1.1, 2.2 or 3.1
select * from tblprice
where wholesale in (1.1,2.2,3.1)
--or
select * from tblprice where wholesale=1.1 or wholesale=2.2 or wholesale=3.1
-- in Vs. between: by in you should provide an acceptable set but by between you should provide an acceptable range
select * from tblprice where wholesale in (1.1,2.2) -- in just accepts values from this set i.e. 1 or 2.2
-- it is the same as the following query
select * from tblprice where wholesale=1.1 or wholesale=2.2
select * from tblprice where wholesale between 1.1 and 2.2 --between accepts any value in this interval including end points
-- it is the same as following query
select * from tblprice where wholesale >=1.1 and wholesale <=2.2
------------------------------------------
/*Adding column*/
----------------------------------------
/* Alter table is using for adding column*/
ALTER TABLE tblGender
ADD explanation nvarchar(50) -- only when you add a column we are not using the column keyword
use mydb
select * from tblGender
--------------------------------------------
/*changing values of column*/
-------------------------------------------
--when you want to change the values inside the table you are using the update clause
update tblgender
set explanation='man'
where gender='male'
update tblgender
set explanation='woman'
where gender='female'
select * from tblGender
--or
select id,explanation,Gender from tblGender
use dbstor
create table price(item varchar(20) not null,wholesale money)
insert into price values ('apple',2.2),('orange',1.3),
('banana',1.1),('grape',3.3)
select * from price
drop table price
--EX: we want to add 0.15 cent to wholesale column
select Item, wholesale, Wholesale +0.15 as [new wholesale]
from price -- as in optional
-- With this method, the original table has not been changed
select * from price
--If you want to change the original table you can add a new column and update its values
--Ex: add a new column and for it's values add 15 cents in wholesale in the price table
alter table price
add [new wholesale] money
select * from price
update price
set [new wholesale]=wholesale+0.15
select * from price
-------------------------------------------
/*Removing/delete a column*/
------------------------------------------
alter table price
drop column wholesale
select * from price
--removing column
-----------------------------------------------
/*Additional Practice*/
-----------------------------------------------
--More Example (page 29 in my slides)
create database metro_uni
use metro_uni
go
--create a table that includes information about students
create table student
(id int not null primary key,
name nvarchar(50),
family nvarchar(50),
GPA float,
city nvarchar(50))
--create a table that includes information about courses that are offered in metro university
create table course
(ID int not null primary key,
name nvarchar(50),
units int)
--create a table that makes a relation between students and the courses that they are taking
create table course_student
(Id int not null primary key,
studentID int references student(ID),
CourseID int references course(ID))
/*Diagram of dependencies*/
--SSMS: In GUI select Databases in Object Explorer ==>refresh ==>press plus sign next to your database
--==>Database Diagrams ==>RC ==>New Database diagram ==>select all tables ==>Add tables ==> close
--time: https://popsql.com/learn-sql/sql-server/how-to-query-date-and-time-in-sql-server
-- https://intellipaat.com/blog/interview-question/sql-interview-questions/#1
--HMW (page 29 in my day1 ppt))
create database metro_uni
use metro_uni
go
--First we should create parent tables
--create a table that includes information about students
create table student
(id int not null primary key,
name nvarchar(50),
family nvarchar(50),
GPA float,
city nvarchar(50))
go
--create a table that includes information about courses that are offered in metro university
create table course
(ID int not null primary key,
name nvarchar(50),
units int)
--create a tables that make a relation between students and courses that they are taken
create table course_student
(Id int not null primary key,
studentID int references student(ID),
CourseID int references course(ID))
/*Diagram of dependencies*/
--SSMS: In GUI select Databases in Object Explorer ==>refresh ==>press plus sign next to your database
--==>Database Diagrams ==>RC ==>New Database diagram ==>select all tables ==>Add tables ==> close
--Or
/* in this method we want to assign a name to our primary and forign key ( user-defined name) so we will use constraint at the beggining and the we use a meaningful name
then the forign key keyword then mention inside the paranthesis the name of foregn key) and then refrences keyword and then the parent table or refrence table and
inside the paranthesis the name of candidate key*/
create table course_student2
(id int not null,
studentID int,
CourseID int,
constraint PK_coursestudent2 primary key(id),
constraint FK_coursestudent2_Student_ID foreign key (studentID) references student(Id),
constraint FK_coursestudent2_course_id foreign key (CourseID) references course(Id))
-------------------------------------------
------------------------------------------------
-------------------------------------------------------------------------------------
/*Mathematical Practice*/
--Print vs. Select
print 9+4
select 9+4
select 9+4 as result --as is optional
select 9+4 result
--you cannot print two different things with one print statement unless you concatenate them
print 9+4 , 9-4 -- you will get error
select 9+4 , 9-4
select 9+4 as addition, 9-4 as subtraction --as is optional
select 9+4 addition, 9-4 subtraction
select -9*4 result
select 9*-4 result
select 19/4 result -- In SQL if two numbers are integers the result of division will be an integer
select 19.0/4 result
select 19/4.0 result
--Remainder
select 19%4 remainder
select 22%6 Remaider
------------------------------------------------
/*Arithmetic functions */
---------------------------------------------------
select abs(-10.4) absolute
select abs(10.4)
select floor(1.72) --the largest value that it is less than or equal to 1.72
select CEILING(1.72) --the smallest integer that is greater than or equal to the 1.72
select floor(-1.72)
select CEILING(-1.72)
select floor(2.4)--gives you the biggest integer less than or equal to it
-- 2 <= 2.4 <= 3
select floor(-2.4)-- note -3 <= -2.4 <= -2
select floor(-2)
--round function removes numbers and increases the unit if it is 5 or bigger
select ROUND(1.72,0) -- the 2nd argument is the number of decimal points that you want to keep
select ROUND(1.42,0)
select ROUND(1.42,1)-- it is going to keep only one decimal number of the original number
select ROUND(1.45,1)
select ROUND(1.5,0)
select ROUND(2.5,0)
select floor(-1.32)
select CEILING(-1.32)
select round(-1.32,0)
select floor(-1.52)
select CEILING(-1.52)
select round(-1.52,0)
select power(2,3)
select power(3,4)--3*3*3*3=81
select power(2,5)=2^3=2*2*2
select power(power(2,3),2)--nested functions --power(power(8,2))=64= 8*8
go
---------------------------------------------
/*Date and Time Functions*/
--part 1 of 2
----------------------------------------------
select getdate() --returns current date and time
select getdate() current_date_and_time
select getdate() [current date and time]
select year(getdate())
select month(getdate())
select day(getdate())
select hour(getdate())--it gives you an error
select datepart(hour,getdate())
select datepart(year,getdate()) as year
select datepart(month,getdate()) as month
select datepart(day,getdate()) as day
select datepart(hour,getdate()) as hour
select datepart(MINUTE,getdate()) as minute
select datepart(second,getdate()) as second
go
---or
select datepart(year,getdate()) as year
,datepart(month,getdate()) as month
,datepart(day,getdate()) as day
,datepart(hour,getdate()) as hour
, datepart(MINUTE,getdate()) as minute
, datepart(second,getdate()) as second
select datepart(year,'2019/08/25') as year
select datepart(month,'2019/08/25') as month
select datepart(day,'2019/08/25') as day
------------------------------------------------------------------------------------------------------------------------------------------------------------------
use dbstor
go
select * from tblprice
--Ex: print a table that has a new column and its name is new wholesale and add 15 cents to wholesale of all items
select Item , wholesale , wholesale +0.15 from tblprice
-- as you can see this new column doesn't have a name so I'm going to use an alias for it
select Item , wholesale , wholesale +0.15 as [new wolesale] from tblprice
-- If you want to use a special character or space in an alias you must put it in a hard(square) bracket
--as is optional
go
--Note: The original table has not been changed
select * from tblprice
go
select wholesale +0.15 as [new wolesale] from tblprice
/*Ex: print a table that has a new column that its name is wholesale after tax and add 13 percent
to wholesale*/
select Item , wholesale , wholesale *1.13 as [wolesale after tax] from tblprice
--Ex: print a table that has a new column with the name of salesprice and give a 50% discount
select Item , wholesale , wholesale*0.5 as salesprice from tblprice
--as is optional
select Item , wholesale , wholesale*0.5 saleprice from tblprice
go
--More example:
select Item , wholesale , -wholesale minus_wholesale from tblprice
go
select Item , wholesale , -wholesale [minus_wholesale] from tblprice--here square bracket is optional
select Item , wholesale , -wholesale [minus wholesale] from tblprice--here square bracket is mandatory
select * from tblprice
/**************where clause******************/
-- we can add condition to filter rows(records)(observations) by using where clause
--NOTE: SQL is not case sensitive at all even for values inside its tables
--EX1: retrieve(get) all of information of apple (apple here is the row, Item is column, in the question it asks we want to know about apple and apple is row
--so we should use where clause because whenever we have a condistion about rows we are gonna use where
select * from tblprice
select *
from tblprice
where item='apple' -- whenever you want to add a condition to filter records you can use the where clause
--or
select * from tblprice where item='apple'
--Note: New versions of SQL are not case-sensitive at all
--Ex2: find all of the information of items that are cheaper than $1.5
select *
from TBLprice
where wholesale < 1.5
--Ex2: find all of the information of items that are cheaper than or equal to$1.21
select *
from TBLprice
where wholesale <= 1.21
go
insert into tblprice values('watermelon',1.51),('pineapple',3.2)
select *
from TBLprice
-- Ex3:--Ex2: find all of information of items that are before orange in alphabetic order
select *
from tblprice
where item < 'orange'-- just comparing alphabetic order
select *
from tblprice
where item < 'o'
-- Note: if you want to sort your result based on any column you must use order by
select *
from tblprice
where item < 'o'
order by item --Sorting alphabetic order
-- in sql the default version is in Ascending order by default ( we have two kind of order Ascending: smallest to largest and descending: Largest to smallest)
select *
from tblprice
order by item --Sorting alphabetic order
select *
from tblprice
order by wholesale
-- to sort in descending(from the largest to smallest) we must specify desc after column name
select *
from tblprice
order by wholesale desc
select *
from tblprice
order by item desc
--------------------------------------------
/*matching*/
-------------------------------------------
/*complete matching*/
-- for complete matching we use equal sign(=)
select *
from tblprice
where item='apple'
go
select *
from tblprice
where item='orange'
go
insert into tblprice values('avacado',3.6)
select * from tblprice
insert into tblprice values('a',2.4)
select * from tblprice
go
/*Partial matching*/
--Note: we use equal sign to check complete match and we use like keyword to check partial match
--Ex: find all information for items that starts with a
select *
from tblprice
where item like 'a%'--% means every thing after a is acceptable even (nothing)
--Ex: find all information for items that ends with a
select *
from tblprice
where item like '%a'
--Ex: find all information for items that contain a in their names
select *
from tblprice
where item like '%a%'
--Ex: find all information for items that the third letter of their names is a
select *
from tblprice
where item like '__a%' --two underscore before a
--Ex: find all information for items that the third letter of their names is a and their names have exactly 5 characters
select *
from tblprice
where item like '__a__'
--Ex: find all information for items that the third letter of their names is a and their names has at least 5 characters
select *
from tblprice
where item like '__a__%'
--Ex. retreive all items that contain a but do not start with a
select *
from tblprice
where item like '%a%' and item not like 'a%'
--Ex. retreive all items that contain a but do not start with a and wholesale of them is not 1.21
select *
from tblprice
where item like '%a%' and item not like 'a%' And wholesale != 1.21
/*Missing values */
--part 1 of 2
/*Note: we can't use = or != with Null because Null means nothing and in SQL you cannot compare with nothing
although you won't get an error, you won't get the result that you expect*/
--Ex: retrieve all items that wholesale of them is missing
select *
from tblprice
where wholesale is null
--Ex: retrieve all items that contain a but wholesale of them is not missing
select *
from tblprice
where item like '%a%' and wholesale is not null
/*Aggregate Functions*/
--part 1 of 2
/*An aggregate function performs a calculation on a set of values and returns a single value.
Except for COUNT(*), aggregate functions ignore null values.
Aggregate functions are often used with the GROUP BY clause of the SELECT statement.
Transact-SQL provides the following aggregate functions:
AVG: calculating the mean or average of aset of numeric values
COUNT: count the number of rows
COUNT_BIG: is used for counting large result sets
MAX
MIN
STDEV
STDEVP
STRING_AGG
SUM: calculates the sum of numeric values
VAR: calculating the variance of a set of numeric values
VARP:calculating the variance of population of a set of numeric values*/
/**********Count****************/
select * from tblprice
select count(* )from tblprice
select count(item )from tblprice
select count(wholesale )from tblprice --NOTE: Except for COUNT(*), aggregate functions ignore null values.
--Ex: find how many apples are in the price table
select count(* )from tblprice
where item='apple'
--or
select count(item )from tblprice where item='apple'
/*Note: count function counts the number of non-missing values of the column that you use as its argument
if you put * it is gonna count all records with that condition*/
select count(wholesale )from tblprice where item='apple'
--Ex: find how many items are in the price table that we don't know their wholesale
--==> number of missing values of wholesale
select count(* )from tblprice where wholesale is null
--
select count(* ) number_of_missing_values from tblprice where wholesale is null
--or
select count(* ) [number of missing values] from tblprice where wholesale is null
--Ex: find how many items are in the price table that starts with a
select count(*) from tblprice where item like 'a%'
go
-- Ex: find the sum of values for wholesale in the price table
select sum(wholesale) sum_of_prices from tblprice --sum,avg,var,stdev work only for numeric variables
insert into tblprice values ('apple',2.3),('orange',1.0),
('apple',1.5),('grape',3.5),('apple',2.3),('orange',1.0),
('apple',1.5),('grape',3.5)
select * from tblprice
--Ex: find how many apples are in the price table
select count(*) from tblprice where item='apple'
-- Ex: find the average of values for wholesale in price table
select avg(wholesale) average_of_prices from tblprice
-- Note : Missing values are not counted here for the number of obs.
select avg(wholesale) [average of prices] from tblprice
--------------------------------------------------
/*Group by*/
-------------------------------------------------
select * from tblprice
-- Ex: find number of each item in tblprice
select item, count(*) number
from tblprice
group by item --Hint: If you have any non-aggregate columns in select you have to group by them: here item is
--non aggregate and wholesale is aggregate column because it has numerical values
-- Ex: find the average of wholesale for each item in tblprice
select item, avg(wholesale) [average of prices]
from tblprice
group by item --Hint: If you have any non-aggregate columns in select you have to group by them
--Ex: find or retrieve sum of price for each category of items
select item, sum(wholesale) [Sum of prices]
from tblprice
group by item
go
select * from tblprice
-- Ex: find the number of each item that the price of them is less than 2.3
-- Since we need to add condition to filter records(observations)(rows) , we use where clause
select item,count(*)
from tblprice
where wholesale < 2.3 -- Unlike SAS ,it is not consider null values(in SAS null values are considered less than any number that you can assume)
group by item -- If you have any non-aggregate column in select you must group by them
-- Ex: find the sum of each item that the price of them is less than 2.3
-- Since we need to add condition to filter records(observations)(rows) , we use where clause
select item,sum(wholesale)
from tblprice
where wholesale < 2.3 -- it is not consider null(unlike SAS)
group by item-- If you have any non-aggregate column in select you must group by them
/* so far we talked about where clause, group by, order by, now we are going to talk about having clause, where is using in the situation that we have some conditions
on rows not columns, having is using when we want to apply condition on the results that we got from grouping*/
--Ex: find or retrieve the sum of the price for each category of items if the sum of the price is less than $2.3
--we want to add conditions to filter groups. For filtering groups, you must use Having
--------------------------------------------------
/*Having:The HAVING clause in SQL is used to filter the results of a GROUP BY operation based on a condition or an aggregate function's result.
It allows you to specify conditions that apply to grouped rows, and it is typically used in combination with the GROUP BY clause*/
-------------------------------------------------
/*Here's when you would use the HAVING clause:
1: Aggregating Data: When you have performed a GROUP BY operation to group rows based on a particular column or columns and you want to filter the groups
or aggregations based on some condition.
2: Filtering Grouped Data: When you want to apply a condition to the result of an aggregate function (e.g., SUM, COUNT, AVG, etc.) for each group.
3: Post-Aggregation Filtering: When you need to filter the result set after aggregation. It's different from the WHERE clause,
which filters rows before aggregation.
For filtering records(obs)(rows) we use the where clause but
For filtering groups, you must use the Having clause
*/
select * from tblprice
--EX1:find or retrieve the sum of the price for each category of items if the sum of the price is less than $2.3
-- Note: for adding a condition for any group you have to use the "Having" clause after group by
select item, sum(wholesale) [Sum of prices]
from tblprice
group by item
having sum(wholesale)<2.3-- note: In SQL you are not allowed to use the alias of select in where or having
-- Ex2: find the average of wholesale for each category of item in tblprice if the average is bigger than 1.5
select item, avg(wholesale) [average of price]
from tblprice
group by item
having avg(wholesale) >1.5 -- Hint: you are not allowed to use an alias ([average of price]) of select in having
--------------------------------------------------
/*Having Vs. Where*/
-------------------------------------------------
/*Note:
1.for adding condition to filter records(observation) we use where clause after from clause and
for adding conditions to filter groups you have to use the "Having" clause after group by clause
2. we are not allowed to use an aggregate function in where clause, If you want to filter rows based on the results of an aggregate function,
you typically use the HAVING clause instead of the WHERE clause. The HAVING clause is used in conjunction with GROUP BY to filter the results of aggregate functions.
*/
-- Ex: Find the average of wholesale for each item in tblprice if wholesale is bigger than 1.5 for those items
/*we want to filter observations and for those items that their wholesale is bigger than 1.5
We want to calculate average of their price*/
select item, avg(wholesale) [average of prices]
from tblprice
where wholesale >1.5
group by item
-- Ex: Find the average of wholesale for each item in price and report the result if it is bigger than 3
/*we want to group by items and calculate the average of wholesale and then filter groups */
select item, avg(wholesale) [average of prices]
from tblprice
group by item
having avg(wholesale)>3
-------------------------------------------Practice-------------------------------------------------------------------------
/*Ex: Find the average of wholesale for each item in tblprice that they are more expensive than 1.5 and report
the result if the average is bigger than 3 */
/*we want to filter observations and for those items that their wholesale is bigger than 1.5
We want to calculate average of their price and then filtering groups (if the result is bigger than 3 we want
to see it)*/
select item, avg(wholesale) [average of prices]
from tblprice
where wholesale>1.5
group by item
having avg(wholesale)>3
/* Ex: Find sum of wholesales for each item in tblprice if wholesale of that item bigger than
1.5 and the sum of them is bigger than $4
for those items that their price is bigger than 1.5 (Filtering obs)we will calculate sum of their price and
we will report them if the result is bigger than 4(filtering groups)*/
select item,sum(wholesale) sum
from tblprice
where wholesale>1.5
group by item
having sum(wholesale)>4
/*Ex: Find sum of wholesale for each item in price table if wholesale is bigger than 1.5 and sum of
them is bigger than $4 and print result from smallest to largest(ascending order)*/
--------------------------------------------------
/*order by */
-------------------------------------------------
select item,sum(wholesale) [sum of price]
from tblprice
where wholesale>1.5
group by item
having sum(wholesale)>4
order by [sum of price]-- Note you are allowed to use alias of select clause only in order by
-- by default, order by will order the result in ascending order
/* Ex: Find sum of wholesale for each item in price table if wholesale is bigger than 1.5
and sum of them is bigger than $4 and print result from largest to smallest (descending order)*/
select item,sum(wholesale) [sum of price]
from tblprice
where wholesale>1.5
group by item
having sum(wholesale)>4
order by [sum of price] desc
--Note: you are allowed to use alias of select clause only in order by clause
/* order of clauses in select statement: this is the acronym that is helpful to remmenber the sequence of select statments
Some Female Workers Go Home Ontime.
select...
From...
where ...
group by ...
having ...
order by ...
*/
-- Hmw2.1: Find average of wholesale for each item in price table if wholesale is bigger than 1.5 and
--show the result in descending order
-- HMW2.2: Find average of wholesale for each item in price table if wholesale is bigger than 1.5 and
--show the result in ascending order based on average
/*hmw2.3 Find sum of wholesale for each category item in price table if wholesale is bigger than
1.1 and show result is descending order just for those group that has a sum less than 6.5 */
use dbstor
select * from tblprice
select item, avg(wholesale) [average of prices]
from tblprice
where wholesale>1.5--, desc
group by item
having avg(wholesale)>3
评论
发表评论