Practice SQL questions based on store database page 95

Document Sample
Practice SQL questions based on store database page 95 Powered By Docstoc
					Practice SQL questions based on store database page 95 of textbook

   1. List the last and first names of all employees whose age is >40 years.
              Select emp_lname, emp_fname
              From employee
              Where emp_dob < #11/20/1961#;

                                   emp_lname emp_fname
                                   Greenboro        Lottie
                                      Smith         Robert
                                     Ogallo        Roberto
                                    Eindsmar         Jack
                                     Archialo       Barry




   2. List the last and first names of all employees who work in stores that have made
      more than $1,000,000.00 in YTD sales, sorted by employee last name.

              Select emp_fname, emp_lname
              From employee, store
              Where store_ytd_sales > 1000000
              And store.store_code = employee.store_code
              order by emp_lname;

                                   emp_fname emp_lname
                                   Barry         Archialo
                                   Jack          Eindsmar
                                   Elizabeth     Johnsson
                                   Robert        Mckee
                                   Howard        Olenko
                                   Nancy         Ratula
                                   Cary          Renselaer
                                   Jennie        Rumpersfro
                                   Alan          Washington




   3. List the total sales of all stores in the West region
              Select store_ytd_sales
              From store, region
              Where region.region_descript = “West”
              And region.region_code = store.region_code;

                                       store_ytd_sales
                                            $1,003,455.76
                                            $1,421,987.39
                                             $944,568.56
4. List the names of stores whose YTD sales is greater than the average YTD sales
   across all stores.
           Select store_name
           From store
           Where store.store_ytd_sales >
           (select avg(store_ytd_sales)
           from store);

                                             store_name
                                           Primary Key Point


5. How many employees do we have whose last name starts with the letter „R‟.
        Select count (*)
        from employee
        where emp_lname like “R*”;

                                              Expr1000
                                                       5




6. How many employees work in the “West‟ region?
        Select count (*)
        From employee, store, region
        Where employee.store_code = store.store_code
        and store.region_code = region.region_code
        And region.region_descript = "West";

                                              Expr1000
                                                     12


7. Reduce the YTD sales of “Attribute Alley” by 15%. BE CAREFUL NOT TO
   RUN THIS QUERY MORE THAN ONCE. (this query will not show any results
   on your screen. Question Number 8 will be used to show the results of this query)
          Update store
          set Store_ytd_sales = store_ytd_sales *.85
          Where store_name = "Attribute Alley";

8. Display the entire contents of the store table.
          Select (*)
          From store;

          STORE_CODE STORE_NAME STORE_YTD_SALES REGION_CODE EMP_CODE
                     1 Access Junction            $1,003,455.76   2   8
                     2 Database Corner            $1,421,987.39   2   12
                     3 Tuple Charge                 $986,783.22   1   7
                     4 Attribute Alley              $802,883.28   2   3
                     5 Primary Key Point          $2,930,098.45   1   15
9. Add a new sixth store whose name is „Foreign Keystone‟. This is will be store 6.
   We have not yet decided in which region it will be located or who will be its
   manager. Then display the entire contents of the store table.
          Insert into store
          Values ("6","Foreign Keystone",0,null,null);

           Select *
           From store;

          STORE_CODE STORE_NAME STORE_YTD_SALES REGION_CODE EMP_CODE
                    1 Access Junction        $1,003,455.76   2       8
                    2 Database Corner        $1,421,987.39   2       12
                    3 Tuple Charge            $986,783.22    1       7
                    4 Attribute Alley         $802,883.28    2       3
                    5 Primary Key Point      $2,930,098.45   1       15
                    6 Foreign Keystone               $0.00




10. How many employees have no middle initial.
         Select count (*)
         From employee
         Where emp_initial is null;

                                          Expr1000
                                                 2