-Functions and multi-table queries
- =====================================================================================================
-in the Pubs database, complete the following query
-1. use inner join to query the information of authors and publishers in the same city in the tables of authors and publishers.
Use the bar
go to
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 the author
Internal join publisher
About the author. City = publisher. city
-2. Find out all authors whose author numbers begin with 1~5, and use right join in the query result set.
-List the names of publishing houses in the author's city.
select au_lname，au_fname，b.pub_name from authors a
Right external connection
Publisher b
In city a = city b
Where au_id is similar to "[1-5]%"
-3. Use self-join to find authors who live in the same postal code area in Auckland.
select distinct a.au_lname，a.au_fname
From author a
Internal connection
Author b
on a.zip=b.zip
And a.au _ id < & gtb.au _ id.
Where a.city='Oakland'
-Page 26 of the study manual
- 1.
-2.( 1) Need to obtain the information of external candidates aged between 35 and 40.
Use recruitment
go to
Select from external candidates *
Where datediff(yy, date of birth, getdate ()) is between 35 and 40.
-(2) Advertisements need to be published in newspapers 10 days after the current date,
-The system needs to calculate the date and display it in the following format.
-| Today | 10 days later |
- | - | - |
- | | |
- | - | - |
Select getdate () as today, and dateadd(dd, 10, getdate ()) as [from today 10 day].
-(3) Statistics on the average time interval between the examination and interview date of external candidates.
Select avg(datediff(dd, test date, interview date)) as the average time interval.
From external candidates
-(4) Need to obtain the names of external candidates and the positions they apply for.
Select a. candidate name as candidate name and b. job description as job name.
From external candidate a
Internal connection position b
A. position number = B. position number
-(5) It is necessary to obtain the names of external candidates who applied for 200 1 and the names of recruitment agencies that recommended them.
Select a. candidate's name as external candidate's name and b. name as recommended recruitment agency's name.
From external candidate a
Join recruitment company b internally.
A. Recruitment company number = B. Recruitment company code
Where datepart(yyyy, application time) =200 1
-(6) Need to obtain the name of the external candidate and the name of the newspaper to which the advertisement citing the photo belongs.
Choose a. candidate's name, C. newspaper name.
From external candidate a
Internal connection advertisement b
About a. Number of advertisements = B. Number of advertisements
Internal connection newspaper c
On B. newspaper number = C. newspaper code
-(7) Need to obtain a list of university names, newspaper names and their addresses.
Select the university name as the name and the university address as the address from the university.
alliance
Select the newspaper name and address from the newspaper.
-P27 Homework on the computer
-( 1) displays the report of all shipments in the following format (delivery days = actual arrival date-delivery date).
-| Order number | Delivery date | Actual arrival date | Delivery days |
- | - | - | - | - |
- | | | | |
- | - | - | - | - |
Using GlobalToyz
go to
Select the order number and the delivery date as the delivery date.
Actual arrival date, datediff(dd, delivery date, actual arrival date) as delivery days.
Judging from the transportation situation.
-(2) Display all orders in the following format.
-| Order number | Shopper number | Order date | Day of the week |
- | - | - | - | - |
- | | | | |
- | - | - | - | - |
Select order number, shopper number, and order date as [date (number)].
What day is today?
From the order
-(3) Display the names of all toys and their category names.
Select toy name and category name.
From toy a
Internal join category b
About the classification number. = B. Category number
Choose the toy name and category name from toys A and 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 and category name.
From toy a
Internal join category b
About the classification number. = B. Category number
Internal connection trademark c
About a. Trademark = C. Trademark number
Choose toy name, brand name and category name from toys A, Category B and Trademark C.
In which a. category number = B. category number, a. trademark = C. trademark number.
-(5) The format shows the toy's order number, toy ID and description of the gift package used by the toy.
-| OrderNo. | Toyno. | Packaging information |
- | - | - | - |
- | | | |
- | - | - | - |
Select order number, toy number and information as packaging information.
From the order details
-(6) Display the names of all shoppers and their purchase order information (whether the shoppers have orders or not).
-| Shopper's name | Order number | Order time | Order amount |
- | - | - | - | - |
- | | | | |
- | - | - | - | - |
Select the name as the name of the shopper, the order number, the order date as the order time, and the total price as the order amount.
From shopper a
Left outer join order b
A. Shopper number = B. Shopper number
-(7) Display the order number, order date and season of each order in the following format.
-| OrderNo. | Order Date | Season |
- | - | - | - |
- | | | |
- | - | - | - |
Select the order number, order date and date part (qq, order date) as the season.
From the order
-(8) Display the ID, name, telephone number of all shoppers and the recipients of the corresponding orders.
-| ShopperNo。 | Name | Phone | Recipient's name | Phone |
- | - | - | - | - | - |
- | | | | | |
- | - | - | - | - | - |
Select a. shopper number, a. name, a. phone number, c. recipient name, c. phone number.
From shopper a
Internal join order b
A. Shopper number = B. Shopper number
Internal coupling receiver c
B. Order number = C. Order number
-(9) Display the names and addresses of all shoppers and recipients.
-| Name | Address |
- | - | - |
- | | |
- | - | - |
Select name as the name, recipient address
alliance
Choose a name and address from the shoppers.
-( 10) Displays the names and sales quantities of all toys.
Select the toy name and sum (sales quantity) as the total sales quantity.
From toy a
Monthly sales of left external connection b
Toy number = toy number
Group by toy name
-( 1 1) displays the names of the top 3 shoppers who spent the most in May of 200 1 year and their spending amounts.
Select the top three as the names of shoppers, and sum (total price) as the consumption amount.
From shopper a
Internal join order b
A. Shopper number = B. Shopper number
The order date is between "200 1-05-0 1" and "2001-05-3123: 59: 59".
Group by name
Sort by desc sum
- =======================================================================
-Sub-query
- =======================================================================
-P3 1 Learning Manual Computer Test
-( 1) Lists the recruitment companies in the city where the external candidate "Chen Xiaoxiao" is located.
Use recruitment
go to
Select * from recruitment company
In which city?
(Choose a city from external candidates.
In which candidate's name =' Chen Xiaoxiao')
-(2) List the names of candidates with "network capability"
Select the name of the candidate from the external candidates.
Where is the candidate code?
(
Select a candidate code from candidate skills.
Where is the skill number?
(
Select skill number from skills.
Where skill description = "network capability"
)
)
-(3) List the recruitment companies that have not recommended candidates.
Select * from recruitment company
Where to recruit company code
be not in/be dead
(Select the recruitment company number from the external candidates.
Where the recruitment company number is not empty)
-(4) List the information of external candidates whose test scores are above the average score of all external candidates.
Select from external candidates *
Where the test score >
(Select average from external candidates)
-(5) List the information of external candidates whose test scores are above the average score of candidates in each "position".
Select * from external candidate as,
(Choose the job number and avg (test score) as the average score.
From external candidates
Group by job number) as B.
Where a. position number = B. position number and test score > average score.
-(6) List the names of employees and the number of skills they have.
Select employee name and skill number.
From employee A, (select employee number and count (skill number) the number of skills owned by employee skill group according to employee number) b.
Among them, a. employee no. = B. Employee number
-(7) Seek the total salary of all employees in the sales department in 200 1 year.
Select the total amount from salary as the total salary.
Where datepart(yy, date of payment) =200 1, and the employee number is.
(Select employee number from employees.
Where the department number =
(Select the department number from the department.
Where Department Name =' Sales Department')
)
-Computer operation
-( 1) Inquire about the purchase order of "Whaling" toys.
Using GlobalToyz
go to
Select * from the order details where the toy number is located.
(Select toy number from toys, where toy name = "Whaling")
-(2) Inquire about toys whose prices are lower than the average prices of all toys.
Choose from toys *
Where is the price?
(Choose the average price from toys)
-(3) Inquire about toys whose prices are higher than the average prices of similar toys.
Choose from toy a *
Where the price >
(Choose the average value (price) from Toy B)
Where a. Category number = b. Category number grouped by category number)
-(4) Inquiring about the information of unsold toys (realized in two ways)
Choose from toys *
Where is the toy number?
(Select the number of toys from the monthly sales)
Choose from toy a *
A place that doesn't exist
(
Select from monthly sales b *
Where a. Toy number = b. Toy number
)
-(5) Query the names of toys with the highest and lowest prices.
-| highest price | lowest price |
- | - | - |
- | | |
- | - | - |
Select (select toy name from toy where price = (select max (price from toy)) as has the highest price.
(Choose toy name from toys, where price = (choose the lowest price from toys)) as has the lowest price.
-(6) Who is the consignee of the order purchased by customer "Larson"?
Select * from acceptor, where the order number is in
(Select the order number from the order, where shopper number =
(Select the shopper number from the shoppers, where name =' Larson'))
-(7) Query the category information of toys with more than three categories in the toy category.
Select * from category where the category number is in
(choose the category number from the toy group according to the category number with a count of > 3) I hope these are helpful to you. This is a common example of multi-table query. What you don't understand can be discussed, or write down what kind of problems you want to solve!