Write sql queries, PL-SQL Programming

Write SQL queries to solve the following specifications. Include the query AND THE OUTPUT.  A screen dump of the output is acceptable. Show as many rows as you can. A screen dump is usually done via the ALT/PRNTSCRN command sequence.

1.  Display full details for all cars

2.  Display the model name and car group name for all models. Order by car group name in alphabetical order.

3.  Display full details for all bookings where the amount due has not yet been entered.

4.  Display the customer number for all customers who have ever rented a car. Do not display duplicates.

5.  Display the total number of miles travelled for each booking and sort the output by the number of miles travelled in descending order. Label the output: MILES TRAVELLED

INTERESTING EXERCISE: Is it possible to sort using an alias?

6.  Display full details for all bookings where the miles out was greater than 300 and the booking has been paid.

7.  Display full details for any customer  who

1)  Has an address where the second character is a 2 and whose customer number has 3 as the second character  OR

2)  Has a customer number greater than or equal to 400 and lives in a county whose name begins with an H

8.  Display the rate per mile for all cargroups where the cargroup name is A1 or A2 and the rate per day is either 25 or 33. Find a way of optimising your code so that the complete SQL statement has only logical operator (AND, OR, NOT).

9.  Display the customer number and the  booking number for all bookings. Order by booking number within customer number (both ascending).  Your query must not contain the word WHERE.

10. Using a traditional join (WHERE CLAUSE) display the customer number and the  booking number for all bookings. Order by booking number within customer number (both ascending).  Your query must use the alias A for I_BOOKING and the alias B for I_CUSTOMER.

Posted Date: 2/28/2013 2:25:06 AM | Location : United States







Related Discussions:- Write sql queries, Assignment Help, Ask Question on Write sql queries, Get Answer, Expert's Help, Write sql queries Discussions

Write discussion on Write sql queries
Your posts are moderated
Related Questions
Cursors The Oracle uses work areas to execute the SQL statements and to store process information. A PL/SQL construct known as the cursor. Let's you assume name a work area and

MERGE and TRUNCATE in SQL SQL has two more table update operators, MERGE and TRUNCATE. MERGE, like INSERT, takes a source table s and uses it to update a target table t. Brief

Complete the following steps to create a procedure to calculate the tax on an order. The BB_TAX table contains the states that require taxes to be submitted for Internet sales. If

Brewbean's is implementing a new discount for return shoppers - every fifth completed order receives a 10% discount. The count of orders for a shopper is placed in a packaged varia

Using Pragma RESTRICT_REFERENCES: The function called from the SQL statements should obey certain rules meant to control the side effects. To check for violation of the rules,

Query: SELECT * FROM EMPLOYEE1; Select 5 columns and all rows from one table Query: SELECT C_ID, COMPANY, BUILDING, DEPARTMENT, BRANCH FROM CONTRACT;

Effects of NULL Operator As a general rule-but not a universal one-if NULL is an argument to an invocation of a system-defined read-only operator, then NULL is the result of t

Cursor Variables As Parameters You can declare the cursor variables as the formal parameters of the functions and procedures. In the illustration below, you define the REF CUR

Implicit Cursors The Oracle implicitly opens a cursor to process each SQL statement not related with an explicitly declared cursor. The PL/SQL lets you refer to the most recen

Example of WRAP Operator - SQL The effect of Example can be obtained in SQL but note that one needs to write down not only the names of the columns being wrapped but also the