- Questions & Answers
- + Post
- Get Coins
2) Explain how and when it is appropriate to use the following
a. GROUP BY vs. ORDER BY
b. WHERE vs. HAVING
date = date
social security number = varchar2
phone = number
GROUP BY VS ORDER BY
Group By : This will summarize unique combination of column values.
Order by : This is used to sort and display the data based on the specified columns.
See here it is displaying the product table data based on the column name in sorting order(ascending order by default) .
Where vs Having:
Where : this is used to display the data based on the condition specified in the where clause.The Difference between where and having is , we cannot use "where" clause on the aggregates by we can use "having" on aggregates.
In general we have to use "where" before "group by" but we have to use "Having" after "group by".Thats why we call "where" as a pre-filter and "Having" as a post filter.
If you were building a database table that contains the following attributes/fields: date, social security number,...
A Pages entity in a relational database for tracking web browsing has the following attributes: PageID (the primary key, an integer number, required), PageURL (text, maximum 255 characters, required), IPAddress (text, maximum 15 characters, required), ViewDateTime (date and time the page was viewed, required). (a) Write an SQL Data Definition Language command to create the table for this entity. (b) Write an SQL Data Definition Language command to add a column for a new attribute to this table: ViewDuration (duration...
Question 1: University Database Consider the following set of requirements for a UNIVERSITY database that is used to keep track of students' transcripts. The university keeps track of each student's name, student number, social security number current address and phone, permanent address and phone, birth date, sex, class (freshman, sophomore,., graduate), major department, minor department (if any), and degree program (B.A., B.S.,..., Ph.D.). Some user applications need to refer to the city, state, and zip of the student's permanent address,...
DATABASE SYSTEMS CLASS: 1. You have a table that contains the following fields: MemberLastName, MemberFirstName, Street, City, State, PostalCode, and MembershipFee. There are 75,000 records in the table. What indexes would you create for the table, and why would you create these indexes?
A University database contains information about students (identified by social security number, or SSN) and courses (identified by courseid). Students enroll in courses; each of the following situations concerns the Enrolls-In relationship set. For each situation, draw an ER diagram that describes it (assuming no further constraints hold). 1.Students can enroll in the same course in several semesters, and each offering must be recorded. 2. Students can enroll in the same course in several semesters, and only the most recent...
Programming Assignment This is a database course using Open Office Database. Assume that you have been given the task of creating a system for a library to keep track of their books, the borrowers of the books, and the books that are currently lent out. Your first step will be to create the relations necessary for this system. Book will need information such as a unique identifier for each book, title, author, ISBN number, date of publication, cost. Borrower will...
Multiple Choice Which of the following is not a data validation technique? (a) The Round function (b) Lookup fields (c) Default values (d) Input masks 6. When would you use the Date function (a) To display the current date (b) To display only the year from a date field (c) When doing math involving two date fields (d) To convert a value from a number into a date 2. Which of the following fields is least likely to be set...
c) Write an ER Diagram for the following Library database. Identify all the Entities, Relationships and Attributes. Underline the primary keys and mark the different constraints. You can add an I attribute as a primary key for an entity to make t unique if necessary. Library has a number of branches in the city, each branch having a name, address and librarian. Books have title, authors and publisher. A book can have multiple authors but a single publisher. Note that same...
For this set of Review Questions, we will create and use a database for the Wedgewood Pacific Corporation (WPC) that is similar to the Microsoft Access database we created and used in Chapters 1 and 2. Founded in 1957 in Seattle, Washington, WPC has grown into an internationally recognized organization. The company is located in two buildings. One building houses the Administration, Accounting, Finance, and Human Resources departments, and the second houses the Production, Marketing, and Information Systems departments. The...
The Northwind database created by Microsoft contains the sales data for a fictitious company called Northwind Traders, which imports and exports specialty foods from around the world. Here is the schema of the database: Products (ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued); Suppliers (SupplierID, CompanyName, ContactName , ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax, HomePage); Categories (CategoryID, CategoryName, Description, Picture); Orders (OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName,ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry); Order_details (ID, OrderID,...
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...