QUESTION 21
|
|
A mandatory clause that is at the start of all SQL retrieval
queries : SELCT
A clause used to return only the values in a result table that are
unique : SELECT DISTINCT
A clause used to filter tuples according to some condition or
predicate, like selection in relational algebra: WHERE
A clause that is used to list the tables and any joins required in
a query : ON
An operator that allows multiple values to be specified in a
comparison statement : IN
An operator used to assign an alias to a table or attribute :
AS
If used it must be the last clause in a query: HAVING
Allows tuples which have the same value for one or more attributes to be grouped together and treated as a unit when using aggregate functions : GROUP BY
A clause which can be used to filter groups that appear in the final result table
An operator used to create a table with all rows from two tables : UNION
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(n) __________________ is a logical connection between rows in two tables where data in one table references data in another table. The SQL ________________ statement removes a table from a database. The relational algebra "restrict" operator selects only the rows from the input table(s) that satisfy a condition expressed in the _________________ clause. The SQL/PL ______________ command is used to re-direct the output of a script file to a text file. The ________________________ join creates a result set that includes the...
Tables: Create table Item( ItemId char(5) constraint itmid_unique primary key, Decription varchar2(30), Unitcost number(7,2)); Create table Customer( custID char(5) constraint cid.unique primary key, custName varchar2(20), address varchar2(50)); Create table Orderdata( orderID char(5) constraint oid_uniq primary key, orderdate date, shipdate date, ItemId char(5) references Item.ItemId, No_of_items number(4), Unitcost number(7,2), Order_total number(7,2), custID char(5) references customer.custID); Insert Into Item values(‘A123’,’Pencil’,2.5); Insert Into Item values(‘B123’,’Pen’,15); Insert Into...
2) What were the total rental fees for each member by member last name? (w3schools-> SQL Inner Join& SQL Functions Count, Avg, Sum & SOL Group By)- this requires 2 joins 13) What were the total rental fees for members treport Mem num who total fees were greater than $5-change the output label to FeesMoreThan5S? (w3schools->SQL Inner Join & SQL Functions Count, Avg, Sum& SQL Group By & SQL Having) 14) What was the highest fee paid by each member...
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...
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...
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...
Question 9 (1 point) Which choice below describes the function of the following SQL statement? SELECT ID, Last Name, First Name, Address FROM Members WHERE Last Name Last Name a simple query a parameterized query a compound query a wildcard query Question 10 (1 point) The correct SQL syntax to sort a table by Last Name in order of A-Z is SORT BY Last Name ASCENDING ORDER BY Last Name Down ORDER BY Last Name ASC ORDER BY Last Name...
Match each of the following descriptions with the appropriate SQL term. Specifies a subset of columns to appear in the answer set SELECT * Tells SQL to SORT the answer set into a specific sequence ORDER BY Identifies the tables(s) that are used as input to a SQL query WHERE - DELETE Removes duplicate values from the answer set Used with a LIKE condition to indicate a match on any number of characters Specifies a subset of rows to appear...
Topic: Inventory Manangement Part A. Database Creation Your database should have a minimum of 4 tables and include both one-to-many and many-to-many relationships. Be sure to include some numeric and/or date fields. Define all appropriate constraints, using the proper naming conventions (see Structure Notes below). Populate your database with at least 30 records in the main table(s), and whatever is needed in related tables. Submit the following: • a short description of the purpose of the database and what the...
Summarize these pages in your own style and you have to include in your report some figures highlighting the relation between these operations. Basic AGGREGATE functions (Revision) COUNT: returns the number of tuples, which meet the specified condition: SELECT COUNT(DISTINCT Dept) AS Num_Depts FROM subject: SUM: returns the sum of the values in a specified column (i.e. numeric column) SELECT COUNT(*) AS hi_sal, SUM(salary) FROM Lecturer WHERE Salary > 4500 MIN: returns the minimum value in a specified column (numeri...