SQL connection (3) -- exercise exercises and SQL exercises

SQL connection (3) -- exercise exercises and SQL exercises

-- 1. Fill in the following query (mark with <#>) to obtain the result.

SELECT e. emp_id, e. fname, e. lname, B. 'name' FROM employee e inner join <1> B ON e. assigned_branch_id = B. <2>. The result is as follows:

 

-- 2. write a query and return the account ID (customer. cust_type = 'I), the customer's federated Personal Identification Number (customer. fed_id) and the name of the product (product. name ).

SELECT c. fed_id, p. 'name' FROM customer cINNER JOIN account aON c. cust_id =. cust_idINNER JOIN product pON. product_cd = p. product_cdWHERE c. cust_type_cd = 'I'; the result is as follows:

-- 3. Construct a query to find all employees whose supervisors are in another department. You need to obtain the ID, last name, and name of the employee.

SELECT e. emp_id, sup. emp_idFROM employee eINNER JOIN employee supON e. superior_emp_id = sup. emp_idWHERE e. dept_id <> sup. dept_id; the result is as follows:

 

-- 4. compile a query, which returns the names of all products and the accounts based on the product (connect to the product table using the product_cd column in the account table). The query results must include all products, this product has no customer account in a timely manner SELECT p. product_cd, p. 'name',. account_idFROM product pLEFT JOIN account aON p. product_cd =. shows the result of product_cd:

 

-- 5. the preceding example is rewritten using other external connection types, and the query results must be the same as SELECT p. product_cd, p. 'name',. account_idFROM account a right join product pON. product_cd = p. shows the result of product_cd:

 

-- 6. write a query to connect the account table to the external tables of indiviual and business. cust_id column) requires one row for each account in the result set, and the queried column contains the account. account_id, account. product_cd, individual. fname, individual. lname and business. nameSELECT. account_id,. product_cd, I. fname, I. lname, B. 'name' FROM account a LEFT JOINbusiness bON. cust_id = B. cust_idLEFT JOIN individual iON. cust_id = I. the result is as follows:

 

-- 7. design a subquery to generate the collection {1, 2, 3 ...... 100} SELECT ones. num + tens. num + 1 FROM (SELECT 0 num union all select 1 num union all select 2 num union all select 3 num union all select 4 num union all select 5 num union all select 6 num UNION ALL SELECT 7 num union all select 8 num union all select 9 num) AS ones cross join (SELECT 0 num union all select 10 num union all select 20 num union all select 30 num union all select 40 num union all select 50 num union all select 60 num UNION ALL SELECT 70 num union all select 80 num union all select 90 num) AS tens; the result is shown in:

........