Connect in SQL (3)--Exercise exercises

--1. Fill in the blanks with the following query (using the <#> tag) to obtain subsequent results.

SELECT e.emp_id, e.fname,e.lname,b. ' Name 'From employee e INNER JOIN <1> bOn e.assigned_branch_id = b.<2>;The results are as follows:    

--2. Write a query that returns the account ID of all non-business customers (Customer.cust_type = ' I '), the customer's federal personal identification number (customer.fed_id) and the name of the product on which the account depends (Product.name).

SELECT c.fed_id,p. ' Name 'From customer CINNER JOIN Account aOn c.cust_id = a.cust_idINNER JOIN Product POn a.product_cd = P.product_cdWHERE c.cust_type_cd = ' I '; The results are as follows:     

--3. Build a query to find all employees who are in another department, need to get the employee's ID, last name, and first name

 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 results are as follows:

--4. Write a query that returns all product names and accounts based on the product (using the PRODUCT_CD column of the account table to connect to the product table).query results need to include all products, timely this product no customer account opening SELECT p.product_cd,p. ' Name ', a.account_idFrom product PLeft JOIN account aOn p.product_cd = A.PRODUCT_CD; the results are as follows:

--5. Rewrite the above example with other outer join types, requiring the same query resultSELECT p.product_cd,p. ' Name ', a.account_idFrom account a right JOIN product pOn a.product_cd = P.PRODUCT_CD; the results are as follows:

--6. Write a query to connect the account table out of the indiviual and business two tables (via account.cust_id column)request the result set each account row, the query column has account.account_id, ACCOUNT.PRODUCT_CD, Individual.fname,Individual.lname and Business.nameSELECT a.account_id, A.PRODUCT_CD,i.fname,i.lname,b. ' Name 'From account a left JOINBusiness bOn a.cust_id = b.cust_idLeft JOIN Individual IOn a.cust_id = i.cust_id; the results are as follows:

--7. Design a subquery to generate the collection {1,2,3......100}SELECT Ones.num + tens.num + 1From (SELECT 0 num UNION allSELECT 1 num UNION AllSELECT 2 num UNION allSELECT 3 num UNION AllSELECT 4 num UNION AllSELECT 5 num UNION AllSELECT 6 num UNION AllSELECT 7 num UNION AllSELECT 8 num UNION AllSELECT 9 num) as ones cross JOIN (SELECT 0 num UNION allSELECT Ten num UNION allSELECT num UNION AllSELECT num UNION AllSELECT + num UNION allSELECT num UNION AllSELECT num UNION AllSELECT, num, UNION allSELECT the num UNION allSELECT Num) as tens; the results are as follows:

........

    

Connect in SQL (3)--Exercise exercises