CREATE TABLE Orders ( OrderID Int IDENTITY NOT NULL, SalesPersonID Int NOT NULL, RegionID Int NOT NULL, OrderDate Datetime NOT NULL, OrderAmount Int NOT NULL )
Which statement will produce the report correctly?
SELECT TOP 5 OrderAmount, SalesPersonID FROM orders
SELECT TOP 5 OrderAmount, SalesPersonID FROM orders ORDER BY OrderAmount DESC
SELECT TOP 5 WITH TIES OrderAmount, SalesPersonID From Orders
SELECT TOP 5 WITH TIES OrderAmount, SalesPersonID From Orders ORDER BY OrderAmount
Functions and Parenthesis
Multiplication, Division and Exponents
Addition and Subtraction
Useful resources, Operator Precedence (Transact-SQL)
DEFAULT and NOT NULL constraints
FOREIGN KEY constraints
PRIMARY KEY and UNIQUE constraints
Useful resources, Constraints
primary query -> sub query -> sub sub query and so on
sub sub query -> sub query -> prime query
the whole query is interpreted at one time
there is no fixed sequence of interpretation, the query parser takes a decision on the fly
Primary query results depend on the sub query results, so sub query my execute first then primary query will execute. Useful resources, Subquery Fundamentals
Each student can enroll into more than one class. Each class can accommodate more than one student.
1 to N
1 to 1
M to N to 1
M to N
N to 1
Useful resources, Many-to-Many Data Relationships
select * from Pers where joining_date from '1/1/2005' to '1/2/2005', job= 'Analyst' or 'clerk' or 'salesman'
select * from Pers where joining_date between '1/1/2005' to '1/2/2005', job= 'Analyst' or job= 'clerk' or job= 'salesman'
select * from Pers where joining_date between '1/1/2005' and '1/2/2005' and (job= 'Analyst' or 'clerk' or 'salesman')
None of the above
All are supported
Useful resources, Data Types
Useful resources, Operator Precedence
A select clause
A where clause
A from clause
A group by clause
A having clause
An order by clause
Useful resources, SQL Queries
You want to sort the result by the date of purchase, starting with the most recently bought book.
Which of the following statements should you use?
SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < '11/11/2002' ORDER BY purchase_date;
SELECT book_title FROM books WHERE price IN (500, 900) AND purchase_date< '11/11/2002' ORDER BY purchase date ASC;
SELECT book_title FROM books WHERE price < 500 OR>900 AND purchase_date DESC;
SELECT Book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < '11/11/2002' ORDER BY purchase date DESC
Useful resources, WHERE Clause