Concept explainers
DISTINCTROW Keyword:
The “DISTINCTROW” keyword omits data based entire duplicate records, not like just duplicate fields. The syntax for “DISTINCT” keyword is as follows:
Syntax:
SELECT DISTINCTROW col_Name FROM table_Name1 INNER JOIN table_Name2 ON table_Name1.col_Name = table_Name2.col_Name ORDER BY table_Name1;
GROUP BY Clause:
The GROUP BY clause is used to group the result of a SELECT statement done on a table where the tuple values are similar for more than one column
Syntax:
SELECT expression1, expression2, expression_n, aggregate_function (expression)FROM table_name WHERE conditions GROUP BY expression1, expression2, expression_n;
INNER JOIN keyword:
“INNER JOIN” keyword is used to select all the matching records of both the table.
Syntax:
SELECT col_Name FROM table_Name1 INNER JOIN table_Name2 ON table_Name1.col_Name = table_Name2.col_Name;
Trending nowThis is a popular solution!
Chapter 7 Solutions
Database Systems: Design, Implementation, & Management
- Using the output shown in Figure P7.12 as your guide, generate a list of customer purchases, including the subtotals for each of the invoice line numbers. The subtotal is a derived attribute calculated by multiplying LINE_UNITS by LINE_PRICE. Sort the output by customer code, invoice number, and product description. Be certain to use the column aliases as shown in the figure.arrow_forwardGiven a small company database tables below. Create the database and tables for the company and answer all the questions below. LINE CUSTOMER VENDOR INV_NUMBER LINE_NUMBER P_CODE LINE_UNITS LINE_PRICE Cus CODE CUS_LNAME CUS_FNAME CUS_INITIAL cus AREACODE CUS_PHONE CUS_BALANCE V_CODE V_NAME V_CONTACT V_AREACODE V_PHONE V_STATE V_ORDER 1000 3 23100-HB 0.05 10010 Ramas Afred 015 844-2573 21225 Bryson, Ine. Smithson 015 223-3234 TN 1006 4 89-WRE-Q 256.00 10011 Dunne Leona 713 804-1238 21220 SuperLoo, Inc. Flushing 04 215-8006 FL N 1 13-Q2/P2 2 14.00 1007 10012 Smith Kathy 015 804-2205 345.88 21231 DSE Supply Singh 815 228-3246 TN 1007 254778-2T 4.00 10013 Olowski Paul 815 804-2100 530.75 21344 Gomez Bros. Ortega 815 889-2546 KY N 1008 1 PVC23DRT 5.87 10014 Orlando Myron le15 222-1872 22507 Dome Supply Smith 01 e78-1419 GA IN 1008 2 WR3/TT3 3 110.05 10015 OBrian Amy 713 442-3381 23110 Randsets Ltd. Anderson 001 078-3008 GA 1008 3 23100-HB 0.05 10010 Brown James 015 297-1228 221.19 24004…arrow_forwardUsing the output shown in Figure P7.12 as your guide, generate a list of customer purchases, including the subtotals for each of the invoice line numbers. The subtotal is a derived attribute calculated by multiplying LINE_UNITS by LINE_PRICE. Sort the output by customer code, invoice number, and product description. Be certain to use the column aliases as shown in the figure. In the SQL Query do the following Select clause: CUS_CODE, INV_NUMBER, P_DESCRIPT, LINE_UNITS AS “Units Bought”, LINE_PRICE as “Unit Price”, derived attribute Subtotal using the formula stated above From: Join conditions between these tables INVOICE, LINE, PRODUCT Where clause: You can put join conditions here as well if you do not want to put them in the from clause. There are many ways to write the query. Order by clause: use the columns as stated in the problem description.arrow_forward
- Problem 12 Using the output shown in Figure P7.12 as your guide, generate a list of customer purchases, including the subtotals for each of the invoice line numbers. The subtotal is a derived attribute calculated by multiplying LINE_UNITS by LINE_PRICE .Sort the output by customer code, invoice number, and product description. Be certain to use the column aliases as shown in the figure. Cus CODE INV NUMBER P DESCRIPT Units Bought Unit Price Subtotal 10011 1002 Rat-tail fie, 1/8-in. fine 2.00 4.99 9.98 10011 1004 Claw hammer 2.00 9.95 19.90 10011 1004 Rat-tail fie, 1/8-in. fine 3.00 4.99 14.97 10011 1008 Claw hammer 1.00 9.95 9.95 10011 1008 PVC pipe, 3.5-in, 8-ft 5.00 5.87 29.35 10011 1008 Steel matting, 48x1/6, 5" mesh 3.00 119.95 359.85 10012 1003 7.25-in. pwr. saw blade 5.00 14.99 74.95 10012 1003 B&D cordless drilI, 1/2-In. 1.00 38.95 38.95 10012 1003 Hrd. cloth, 1/4-in., 2x50 1.00 39.95 39.95 10014 1001 7.25-in. pwr. saw blade 1.00 14.99 14.99 Figure P7.12 Taskarrow_forwardUsing the output shown in Figure P7.12 as your guide, generate a list of customer purchases, including the subtotals for each of the invoice line numbers. The subtotal is a derived attribute calculated by multiplying LINE_UNITS by LINE_PRICE. Sort the output by customer code, invoice number, and product description. Be certain to use the columnaliases as shown in the figure. FIGURE P7.12 SUMMARY OF CUSTOMER PURCHASES WITH SUBTOTALSarrow_forwardQ1: Show all the information from the employee table. SELECT * FROM emp; Q2: Show the employees names and numbers from employee table. SELECT empname, Empno FROM emp; Q3: Show the employees names, salaries and their annual salaries from the employee table, name the last field by annual salary. SELECT empname, sal, sal*12 FROM emp; Or we can change the attribute name using as SELECT empname, sal, sal*12 as "Annual Salary" FROM emp; Q4: Show the employees names, salaries, salaries plus commotion for each employees from employee table. SELECT empname, sal, sal+tcommotion FROM emp; Q5: Show the employees names, salaries, annual salaries plus 100$ for each employees from employee table. SELECT empname, sal, (sal*12)+100 FROM emp; Q6: Retrieve the hire date and department number of the employee(s) whose name is “John" WHERE SELECT hiredate, deptno FROM John'; %3D emp empname Q7. Retrieve the name and job title of all employees who work for the department number 10. SELECT empname, job FROM…arrow_forward
- In our dataset, each job has a predefined salary range. Expand on the previous query and explore this salary range per customer. The query should display the attributes as problem 1 but also include the name of their position, and salary ranges for each employee. Finally, add a column that calculates the difference between each employee’s current salary and the base (minimum) salary for their job. Renamed this last calculated field as “Pay_above_minimum”.arrow_forwardCUSTOMER Customer table is composed of customer number, name and phone number. Give and fill-in the appropriate attribute name for each of the column. custID 123 124 125 126 reservelD 5001 5002 5003 5004 5005 5006 5007 RESERVATION Each reservation is for one taxi. Reservation table is composed of reservation identification number, start reservation date, end reservation date, reservation days requested by customers starting from reservation date until end of reservation date, customer number that make the reservation and taxi number assigned to the reservation. Give and fill-in the appropriate attribute name for each of the column. taxill custName Ahmad Bin Abdullah Fatimah Binti Adam LAI LA2 Ruqayya Binti Idris Sulaiman Bin Daud LA3 LA4 startDate 01/10/2019 05/10/2019 05/10/2019 15/10/2019 20/10/2019 27/10/2019 02/11/2019 taxiType endDate 03/10/2019 12/10/2019 08/10/2019 17/10/2019 25/10/2019 Sedan Sedan Van Van 30/10/2019 04/11/2019 cust Phoneno TAXI Taxi table is composed of taxi…arrow_forwardConsidering Vaccination Database, write queries to: Display the Subject CNIC, Name, Contact, Dose1 Center, and Dose2 Center as shown below: CNIC Name Contact Dose1 Center Dose2 Center 22401-6645321-1 Nasir 3409991112 Lachi Kohat 2 14301-6045321-5 Shahab 3409991112 kohat 3 Dara The subjects who have been vaccinated different types in Dose1 and Dose2. For instance, subjects who were vaccinated ‘Sinovac’ in the first dose, while Pfizer in the second dose.How many Viles of each type have been consumed so far. How many subjects are vaccinated from expired viles. Suggest the name of Incharge for the best performance award based on the highest number of subjects vaccinated on a single dayarrow_forward
- The head of hospital auditing wants to know which patients were charged the most for a treatment. The result table should list three columns from the treatment table appropriately labeled: pat_id, actual_charge, and service _id .Only list rows where the patient was charged the most for a treatment. This may result in some rows where patients were charged identical amounts to other patients. Sort the rows by service_id. Use a correlated subquery. Format the output appropriately.arrow_forwardRead the data into a DataFrame with ID as the index. Convert the “Hired” column into Date/Time data type Create a new column with years of experience with the company at present without rounding. Create a new Boolean column for senior status with employees with at least 10 years of experience as senior and others are not. Create a new column for longevity pay equal to $150 per whole year of experience in the company. Create a list of column names for each data type in the DataFrtame.arrow_forwardFor every order that has been received, display the order ID, the total dollar amount owed on that order (you’ll have to calculate this total from attributes in one or more tables; label this result TotalDue), and the amount received in payments on that order (assume that there is only one payment made on each order). To make this query a little simpler, you don’t have to include those orders for which no payment has yet been received. List the results in decreasing order of the difference between total due and amount paid.arrow_forward
- Np Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:CengageA Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr