12.
A. LEFT JOIN Location lo ON em.LocationID = lo.LocationID
//this query returns all the records in the left table i.e., employees table and matched records from right table if there are no values for particular employee location the result is null. All the employee records are returned along with locations.
13.
B. SELECT * FROM Employee WHERE FirstName LIKE '_A%' AND FirstName NOT LIKE '%Y'
//this query, returns all the records with firstname having second letter A and doesn't have Y as last letter.
14
D. left outer and right outer join
//Full outer join is a combination of left and right combination as it returns all the records which matches either from left and right tables.
15.
A. a number is generated for that column whenever a row is added to the table.
//identity column assigns unique number whenever each row is added usually starts with 0 or 1 and increments by 1 for each new row addition. It is much like autonumber.
16.
C. ORDER BY
//order by sorts the rows in by the followed column name
//any issue with the answer, post in the comment section
QUERY EXAMPLE2 SELECTfrom Employee em (Refer to query example 2 to answer questions 12- 13) 12....
Answer all questions The statement Like "A?9 can be used as a query criterion to match several field values. List any one example of a field value that would match this criterion. 3. What specific type of query would use the following criterion? Like [Enter name:] & 4. What aggregate function would be used to output the number of nonnull field values in a crosstab query? 5. 6. The value Is Null would be used as a criterion in which...
What happens when you try to compile and run the following code? String query = "INSERT INTO Invoices (InvoiceDate InvoiceTotal) " + "VALUES ('2/10/01', '443.55')"; Statement statement = connection.createStatement(); statement.executeUpdate(query); a. A SQLException is thrown at runtime because the executeUpdate method can’t accept a String object. b. A SQLException is thrown at runtime because the SQL statement is coded incorrectly. c. An error occurs at compile time because the SQL statement is coded incorrectly. d. This code compiles and...
Create the database and tables for the database. Show all SQL statements. Include primary and foreign keys. Insert data into each table. Show select statements and display the output of each table. Note:Student’s name must be inserted into table as part of the data! Perform the SQL below: Query one table and use WHERE to filter the results. The SELECT clause should have a column list, not an asterisk (*). State the purpose of the query; show the query and...
The following SQL query returns one row per surgeon. The granularity before GROUP BY is a surgeon-panel combination, in other words, for each surgeon, one panel they performed in. Surgeons can only be listed once per panel, but a log can have more than one panel. SELECT staff.STAFF_NM_WID "surgeon", COUNT( orl.LOG_ID ) "Number of Logs" FROM OR_LOG orl INNER JOIN V_LOG_STAFF staff ON orl.LOG_ID = staff.LOG_ID INNER JOIN OR_LOG_ALL_PROCS procs ON orl.LOG_ID = procs.LOG_ID AND procs.LINE = 1 --First Listed...
1. For each query, there are one or more things to do. Some of these queries use SQL constructs we haven't covered in class but that you should be able to figure out the meaning of. a. SELECT C.custid, C.companyname FROM Sales.Customers AS C LEFT OUTER JOIN Sales.Orders as O ON C.custid = O.custid WHERE O.orderid IS NULL Provide the result set generated by this query. One of the result rows should be (22, Customer DTDMN). What does this tell...
Match the keyword with its function Makes something new, typically a table Provides values to a table Declares a constraint on a table Chooses or reorders columns Declares which tables are present Identifies records of interest Renames either a column or a table Combines two tables or queries with equivalent structure Finds records that are shared between two tables or queries Determines items from a table or query that are not in the other table or query Extends records from...
can someone solve these quick please and thank you! sql chapter 4 Query #1: List the company name, contact name, contact title and the phone number for customers who HAVE NOT put in an order. Use an outer join. Query #2: Create a listing displaying the employee first name, last name and the full name (First name space Last Name) of the person they report to (Supervisor). This is a self-join. If an employee does not report to anyone then...
Which SQL statement retrieves the EmployeeName and City columns from the Employees table? O LIST EmployeeName, City ON Employees; O FIND EmployeeName, City ON Employees; SELECT EmployeeName, City FROM Employees; O RETRIEVE EmployeeName, City FROM Employees; Which SQL statement finds the different cities in which national parks reside with no duplicate rows listed? SELECT DISTINCT city FROM natlpark ORDER BY city; O ORDER BY natlpark DISTINCT city; O ORDER BY city FROM natlpark; O SELECT city FROM natlpark ORDER BY...
QUESTION 21 Complete the table below by matching each definition with the appropriate SQL clause. -A.B.C.D.E.F.G.H.I.J.K.L.M.N. A mandatory clause that is at the start of all SQL retrieval queries -A.B.C.D.E.F.G.H.I.J.K.L.M.N. A clause used to return only the values in a result table that are unique -A.B.C.D.E.F.G.H.I.J.K.L.M.N. A clause used to filter tuples according to some condition or predicate, like selection in relational algebra -A.B.C.D.E.F.G.H.I.J.K.L.M.N. A clause that is used to list the tables and any joins required in a query...
QUESTION 9 You plan to query data using the TRANS_HIST_V view that was created by another user in their schema. Which statement is true? A. The Oracle Server will retrieve the view definition from the ALL_VIEWS data dictionary view. B. The Oracle Server will retrieve data, determined by the query criteria, from the TRANS_HIST_V view. C. The Oracle Server will verify whether you have been granted access privileges to the TRANS_HIST_V view. D. The Oracle Server will automatically reset the...