SQL EXAM

Card Set Information

Author:
mickoneill30
ID:
54057
Filename:
SQL EXAM
Updated:
2010-12-12 08:13:15
Tags:
SQL Exam DCU
Folders:

Description:
Flashcards for the poxy SQL Exam
Show Answers:

Home > Flashcards > Print Preview

The flashcards below were created by user mickoneill30 on FreezingBlue Flashcards. What would you like to do?


  1. Give me a SELECT statement that returns all rows from authors. Sort by au_lname and then by au_fname
    • SELECT *
    • FROM authors
    • ORDER BY au_lname,au_fname
  2. Give me a SELECT statement returns only the rows for authors who only live in the state of California and who do not have an au_lname of Reilly.
    • SELECT *
    • FROM authors
    • WHERE state = 'California'
    • AND au_lname <> 'Reilly'
  3. We have 3 columns in our authors table.
    au_lname
    price
    qty_sold.
    Give me a statement that shows the total revenue per author.
    Select au_lname,qty_sold * price AS revenueFROM authors
  4. Given three tables

    Authors
    au_fname
    au_lname
    au_id

    Titles
    title_id
    title

    TitleAuthor
    au_id
    title_id

    Give me a listing of all Titles by all of the Authors in the Authors table.
    • Select au_fname, au_lname,title
    • FROM authors,titles,titleauthor
    • WHERE authors.au_id = titleauthor.au.id
    • AND titles.title_id = titleauthor.title_id

    This works on the PUBs DB. Make a note that there can be multiple authors for a book. Took me ages to figure that out.
  5. Select all food that costs more than €25 and less than €50
    • SELECT prod_name, prod_price
    • FROM products
    • WHERE prod_price >25 and <50



    • SELECT prod_name, prod_price
    • FROM products
    • WHERE prod_price between 25 and 50
  6. Isnull can cause issues when doing calculcations.
    Select all prod_name from the products table where the value is blank.
    • SELECT
    • prod_name

    • FROM
    • products

    • WHERE
    • prod_price IS NULL
  7. The following example returns the name and the weight for all products for which either the weight is less than 10 pounds or the colour is unknown, or NULL.
    • SELECT
    • Name, Weight, Color

    • FROM
    • Production.Product

    • WHERE
    • Weight < 10.00 OR Color IS NULL

    • ORDER BY
    • Name;
  8. Give me the name of all of the titles sold and which stores they were sold in.

    Sales are stored in the sales table
    Stores are stored in the stores table
    Titles are stored in the titles table
    • SELECT title,stor_name
    • FROM sales
    • INNER JOIN stores
    • ON sales.stor_id = stores.stor_id
    • INNER JOIN titles
    • ON sales.title_id = titles.title_id
    • ORDER BY title
  9. The insert syntax is
    INSERT INTO table(columnX,columnY,columnZ) values(56,'Hello','there')
    Both of the following statements do the same thing

    INSERT INTO CLIENT (ID,FNAME, LNAME) values(6,'Heather',NULL)

    INSERT INTO CLIENT (ID,FNAME) values(6,'Heather')

    If LNAME is a row but you don't insert something in to it then NULL will get inserted automatically
  10. Update Syntax
    UPDATE table SET column = whatever
    WHERE LNAME = 'whatever'
    UPDATE CLIENT SET LNAME = NULL WHERE LNAME = 'Poppins'
  11. Give me a SQL command that calculates the average of the following table (colours)

    COLOUR NUMBER
    Red 7
    White 7
    Blue 1
    White NULL
    Blue 1

    I want White included in the list of results.
    There are two ways to do this.

    • Select colour,SUM(Number)/COUNT(*)
    • FROM colours

    • SELECT AVG(ISNULL(number,0))
    • FROM colours

What would you like to do?

Home > Flashcards > Print Preview