-function and multi-table query
-= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = Complete the following query
-1. use inner join to query the information of authors and publishers in the same city
use pubs
go
select au _ id, au _ lname, au _ fname, phone as au _ phone, address as au _ address.
authors.city,authors.state,authors.zip as au_zip,pub_name, Country
from authors
inner join publishers
on authors. city = publishers. city
-2. Find out all authors whose author numbers start with 1~5. And use the right outer join in the query result set
-list the publishing houses in the same city as the author
select au_lname,au_fname, B.pub _ name from authors a
right outer join
publishers b
on a.city = b.city
where au _ id like' [1-5]%'
-3. Use self-join to find authors living in the same postal code area in Oakland.
select distinct a.au_lname,a.au_fname
from authors a
inner join
authors b
on a.zip=b.zip
and a.au_id< > B.au _ id
where a.city =' Oakland'
-study manual p26
-1. Omitted
-2. (1) Need to get information about external candidates aged between 35 and 4
Use recruitment
Go
. From external candidates
where datediff(yy, date of birth, getdate ()) between 35 and 4
-(2) need to publish an advertisement in the newspaper 1 days after the current date,
-the system needs to calculate the date. According to the following format,
-| Today | 1 days from today |
-|-----------------------------------------------| < --------------------|
select getdate() as Today,dateadd(dd,1, Getdate ()) as [1 days from today]
-(3) Statistic the time average of the interval between the test and interview dates of external candidates
select avg(datediff(dd, test date, Interview date)) as time interval average time
from external candidates
-(4) Need to get the names of external candidates and the positions they applied for
Select A. Candidate name as candidate name, B. job description as job name
from external candidate a
inner join job b
on a. job number = B. job number
-(5) it is necessary to obtain the names of external candidates who applied in 21 and the names of recruitment agencies that recommended them
select a. candidate name as external candidate name, B. Name as Recommended Recruitment Agency Name
from External Candidate a
inner join Recruitment Company B
On A. Recruitment CompanyNo. = B. Recruitment Company Code
where datepart(yyyy, Application time) = 21
-(6) The name of the newspaper to which the advertisement of the external candidate and their reference photo belongs needs to be obtained
Select A. Name of the candidate, C. newspaper name
from external candidate a
inner join advertisement b
on a. advertisement number = B. advertisement number
inner join newspaper c
on B. newspaper number = C. newspaper code
-(7) a list of university names, newspaper names and their addresses is required
. University address as address from university
union
select newspaper name, Address from newspaper
-p27 homework on the computer
-(1) Display the report of all shipments in the following format (delivery days = actual arrival date-delivery date)
-| order number | delivery date | | actual arrival date |
-|-|-----. ---------|
-- | | | | |
-- |----------|---------|--------------|---------|
use GlobalToyz
go
select order number, delivery date as delivery date,
actual arrival date, datediff(dd, delivery date, Actual arrival date) as delivery days
from transportation situation
-(2) all orders are displayed in the following format
-| order number | shopper number | order date (number) |
-|-| | | | | | | | | | | | | | | | | | | | | | | | | | |||||
-|-|-------------------------------------------------------------------------------
datepart(dw, order date) as day of the week
from order
-(3) display all toy names and their category names
select toy names, category names
from toy a
inner join category b
on a. Category number = B. Category number
. Category name from toy a, Category b where a. Category number = B. Category number
-(4) Display the names, trademarks and types of all toys in the following format
-| Toy name | Brand name | Category name |
-|-|-----------------. |
-|-|-|-|-|-|-|-|
Select toy name, brand name, Category name
from toy a
inner join Category B
On A. CategoryNo. = B. CategoryNo.
inner join Trademark C
On A. TrademarkNo.
select toy name, trademark name, category name from toy A, category B, Trademark c
where a. CategoryNo. = B. CategoryNo. and A. TrademarkNo. = C. TrademarkNo.
-(5) The format shows the toy's order number, the toy ID and the description of the gift package used by the toy
-| OrderNo. | | | | | | | | | | | |
-||||| |
-|----------------------------------| |
Select order number, toy number, information as packaging information
from order details < And the order information they purchased (whether the shopper has an order or not)
-| Shopper's name || Order number | Order time |
-|-|-|-|-| | | | | | | | | | | | | | |
-|----------------------------------------------------------------------------------- Total price as order amount
from shopper a
left outer join order b
on a. Shopper number = b. Shopper number
-(7) Display the order number, order date and season of each order in the following format
-| Order number | Order date |
- --------------------------------------------------------------------------------. Order date) as season
from order
-(8) Display all shoppers' ID, names, phone numbers and recipients of the corresponding order
-| Shopper number | name | phone number | recipient name | phone number |
-|-|- -|
-|||||| | |
-| |-|-|---------------------------------------. A. telephone number, C. name of as recipient, C. telephone
from shopper a
inner join order b
on a. shopper number = b. shopper number
inner join recipient c
on b. order number = c. order number
-(9) displays the names and addresses of all shoppers and recipients < p. ----------------------------------------------------------------------------------------- Address from Shoppers
-(1) Displays all toy names and the number of toys sold
select toy names. Sum (sales quantity) as total sales quantity
from toy a
left outer join monthly sales situation B
On A. Toy number = B. Toy number
group by toy name
-(11) shows the top three shoppers who spent the most money in May 21. And that name of three as shoppers whose consumption amount is
select top, Sum (total price) As consumption amount
from shopper a
inner join order b
on a. Shopper number = b. Shopper number
where order date between' 21-5-1' and' 21-5-31 23: 59: 59'
Group by. Desc
-= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =