Detailed analysis of SQL combat of Niuke database (26-30)

SQL26 Summarize the current employees of each department title Number of assignments of type

select de.dept_no, d.dept_name, t.title, count(*)from departments as d right join dept_emp as de on de.dept_no=d.dept_noleft join titles as t on de.emp_no=t.emp_nogroup by de.dept_no,d.dept_name,t.titleorder by de.dept_no,t.title

Their thinking ： Focus on understanding each department title Summary of types .
① First, take the employee number as the connection condition titles Table and dept_emp surface , Get the employee's department number and the employee's corresponding title type , Then take the department number as the connection condition departments surface , Get the Department name , Finally, remember to limit titles Table and dept_emp The time of the table is the current to_date ='9999-01-01'.
② The summary refers to all... Under each department title type , Therefore, the department number should be summarized first , then tltle Summary by type , Aggregate functions count The parameters for title.

SQL29 Use join Search to find movies without classification id

select f.film_id, f.titlefrom film fleft join film_category fc on f.film_id = fc.film_id where fc.category_id is null

Their thinking ： Use film Table is given priority to , Left link film_category surface , The limiting condition is classification id Empty record .

SQL30 Use a subquery to find out what belongs to Action All movies in the category correspond to title,description

select fi.title, fi.description from film fi where fi.film_id in ( select f.film_id from film_category f where f.category_id = ( select category_id from category where name = 'Action' ) )

Their thinking ： Use subqueries twice （ Subquery nested subquery ）, adopt IN The predicate ,film_id,category_id, Nest the three tables layer by layer to query .
author ： Sauce cake

Game programming ️, A game development favorite ~

If the picture is not displayed for a long time , Please use Chrome Kernel browser .