Sample Questions Best site for GRE, LSAT, SAT, GMAT, TOEFL, CCNA, CCSA and interview sample questions  

MCSE Sample Questions ›› SQL 2000 Enterprise Edition

MCSE Sample Questions : SQL 2000 Enterprise Edition

SQL 2000 Enterprise Edition

Sample MCSE Questions on SQL 2000 Enterprise Edition is given here. Technical experts have given the answers. These may differ from General MCSE Sample Questions and Microsoft Windows 2000 Server.

  1. Your supervisor wants you to retrieve the purchase totals for all customers in the UK. No discounts have been honored. The result set should be made up of a CustomerName column and a Total column. Values in the Total column should be listed from highest to lowest. Data in the result set should be grouped by CustomerName.The tables in your database are presented in the following diagram:

    Which of the following SELECT statements would you use to create the above mentioned result set?

    1. SELECT c.CustomerName, od.UnitPrice * od.Quantity as Total
      FROM Customers c Join Orders o ON c.CustomerID = o.CustomerID
      JOIN [Order Details] od ON od.OrderID = O.OrderID
      WHERE C.Country = 'UK'
      GROUP BY c.CompanyName
      ORDER BY Total DESC
    2. SELECT c.CustomerName, SUM(od.UnitPrice * od.Quantity) as Total
      FROM Customers c Join Orders o ON c.CustomerID = o.CustomerID
      JOIN [Order Details] od ON od.OrderID = O.OrderID
      WHERE C.Country = 'UK'
      GROUP BY c.CustomerName
      ORDER BY SUM(od.UnitPrice * od.Quantity) DESC
    3. SELECT c.CustomerName, SUM(od.UnitPrice * od.Quantity) as Total
      FROM Customers c Join Orders o ON c.CustomerID = o.CustomerID
      JOIN [Order Details] od ON od.OrderID = O.OrderID
      WHERE C.Country = 'UK'
      GROUP BY c.CustomerName
      ORDER BY SUM(od.UnitPrice * od.Quantity)
    4. SELECT c.CustomerName, SUM(od.UnitPrice * od.Quantity) as Total
      FROM Customers c Join Orders o ON c.CustomerID = o.CustomerID
      JOIN [Order Details] od ON od.OrderID = O.OrderID
      WHERE C.Country = 'UK'
      ORDER BY Total ASC

    Answer: B

  2. Customers information is recorded in the Customers table. Orders information is recorded in the Orders table. Information in the Orders table is: the ID of the Order, the ID of the Customer who placed the Order, and the Order Date. Details of the Order such as the products purchased, the quantity purchased, and the sales price are recorded in the Order Details Junction Table. Products information is recorded in the Products table.

    The Customers table is linked to the Orders table through the CustomerID FOREIGN KEY column. The Orders table is linked to the Order Details table through the OrderID FOREIGN KEY column. The Products table is linked to the Order Details table through the ProductID column.

    The Order Details table has a composite PRIMARY KEY on the OrderID and ProductID columns.

    What would happen if we were to change the PRIMARY KEY to cover the OrderID column only?

    1. Customers would not be able to purchase less than two products in any given order
    2. Customers would be able to purchase more than one product in any given order
    3. Customers would not be able to purchase more than one product in any given order
    4. None of the above
    5. All of the above

    Answer: C

  3. Your database includes four tables: the Customers table which is referenced by the Orders table, the Orders table which is referenced by the Order Details table, and the Products table which is referenced by the Order Details table. You want to delete all information in your database. Which of the following SQL scripts would you use?

    1. DELETE FROM Customers
      DELETE FROM Orders
      DELETE FROM [Order Details]
      DELETE FROM Products
    2. DELETE FROM Products
      DELETE FROM Customers
      DELETE FROM Orders
      DELETE FROM [Order Details]
    3. DELETE FROM Orders
      DELETE FROM [Order Details]
      DELETE FROM Products
      DELETE FROM Customers
    4. DELETE FROM [Order Details]
      DELETE FROM Products
      DELETE FROM Orders
      DELETE FROM Customers

    Answer: D

  4. Which of the following UPDATE statements are incorrect.(Select all that apply)

    1. UPDATE Products
      SET UnitsOnOrder = od.quantity
      FROM [Order Details] od, Products p
      WHERE p.ProductID = 2 AND od.Quantity =
      (SELECT MAX(Quantity) FROM [Order Details] WHERE ProductID = 2)
    2. UPDATE Products p
      SET UnitsOnOrder = od.quantity
      FROM [Order Details] od
      WHERE p.ProductID = 2 and od.Quantity =
      (SELECT MAX(Quantity) FROM [Order Details] WHERE ProductID = 2)
    3. UPDATE Products
      SET p.UnitsOnOrder = od.quantity
      FROM [Order Details] od, Products p
      WHERE p.ProductID = 2 and od.Quantity = (SELECT MAX(Quantity) FROM [Order Details] WHERE
      ProductID = 2)
    4. UPDATE Products
      SET UnitsOnOrder = od.quantity
      FROM [Order Details] od
      WHERE Products.ProductID = 2 and od.Quantity = (SELECT MAX(Quantity) FROM [Order Details] WHERE
      ProductID = 2)

    Answer: B, C

  5. Your Marketing Department wants to know how many orders each customer placed during the month of December together with the average sales totals for each customer. The department will use this information to grant discounts to eligible customers. You are required to retrieve the total number of orders and the average sales for each customer. A value of zero (0) should be returned for customers who have not placed any orders in December.

    The tables in your database are presented in the following diagram:

    Which of the following SELECT statements would you use to create the above mentioned result set?

    1. SELECT c.CustomerName, COUNT(o.OrderID) As NumofOrders, AVG(ISNULL(od.quantity *
      od.unitprice,0)) As AVGSales
      FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
      LEFT JOIN [order details] od ON od.OrderID = o.OrderID
      WHERE O.OrderDate BETWEEN '2003-12-01' AND '2003-12-31'
      GROUP BY c.CustomerName
    2. SELECT c.CustomerName, COUNT(*) As NumofOrders, AVG(od.quantity * od.unitprice) As AVGSales
      FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
      LEFT JOIN [order details] od ON od.OrderID = o.OrderID
      WHERE O.OrderDate BETWEEN '2003-12-01' AND '2003-12-31'
      GROUP BY c.CustomerName
    3. SELECT c.CustomerName, COUNT(o.OrderID) As NumofOrders, AVG(ISNULL(od.quantity *
      od.unitprice,0)) As AVGSales
      FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
      LEFT JOIN [order details] od ON od.OrderID = o.OrderID
      WHERE O.OrderDate BETWEEN '2003-12-01'
      GROUP BY c.CustomerName
    4. SELECT c.CustomerName, COUNT(o.OrderID) As NumofOrders, AVG(ISNULL(od.quantity *
      od.unitprice,0)) As AVGSales
      FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
      JOIN [order details] od ON od.OrderID = o.OrderID
      WHERE O.OrderDate BETWEEN '2003-12-01' AND '2003-12-31'
      GROUP BY c.CustomerName

    Answer: A

  6. You are required to generate a year-to-date sales report for your Marketing Department. You have to include only customers who have purchased products and only products that have been purchased.

    Which of the following JOIN statement would you use to generate the report?

    1. Customers LEFT JOIN Products
    2. Customers RIGHT JOIN Products
    3. Customers INNER JOIN Products
    4. Customers FULL JOIN Products

    Answer: C

  7. You want to make data in the SSN column in a table unique. However, you want the column to be able to accept NULL values. Which of the following constraint would you use to implement the solution?

    1. PRIMARY KEY constraint
    2. UNIQUE constraint
    3. FOREIGN KEY constraint
    4. DEFAULT constraint

    Answer: B

  8. You are the DB Designer for ABC International Airport's Airport Management Center. You and your colleagues design and maintain the database backbone used by the various departments in the airport. You and your fellow colleague, Steve, are responsible for designing and maintaining all databases that pertain to reservations and flights. You want to include a PassportNumber column in the Passengers table so that the passenger's passport number can be used when placing future reservations. No two passengers have the same passport number the world round. Thus, you want information in that column to be strictly unique. Information in the Passengers table has already been made unique by the PassengerID PRIMARY KEY column.

    What constraint would you use to enforce the uniqueness of the PassportNumber column?

    1. A FOREIGN KEY constraint
    2. A PRIMARY KEY constraint
    3. A CHECK constraint
    4. A UNIQUE constraint
    5. A DEFAULT constraint
    6. A NULL/NOT NULL constraint

    Answer: D

  9. You are the DB Designer for ABCD Airlines. The company maintains a large fleet and a huge flight schedule. Not only is it imperative that information about flights, aircraft, and pilots be up-to-date; it is equally important that all data integrity be optimized to the highest degree.

    You are required to create the tables used to keep track of pilots and aircraft. Every pilot in the company has a batch number and every aircraft has a serial number. It is important that at any given time, flight controllers can know which aircraft had been flown by which pilot and when. Information about pilots in recorded in the Pilots table. Information about aircrafts is recorded in the Aircrafts table. You want a table that will facilitate the matching and combining of different aircraft with different pilots at certain dates.

    You would:

    1. Create an AircraftSerialNumber column in the Pilots table
    2. Create a PilotBatchNumber column in the Aircrafts table
    3. Create a PilotBatchNumber column in the Aircrafts table and create an AircraftsPilots table and include the PilotBatchNumber from the Aircraft table. Make the PilotBatchNumber the PRIMARY KEY of the AircraftsPilots table and include a Date column.
    4. Create an AircraftSerialNumber column in the Pilots table and create an AircraftsPilots table and include the AircraftSerialNumber from the Pilots table. Make the AircraftSerialNumber the PRIMARY KEY of the AircraftsPilots table and include a Date column.
    5. Create an AircraftsPilots table and include the PilotBatchNumber from the Pilots table and the AircraftSerialNumber from the Aircrafts table. Make the PilotBatchNumber and the AircraftSerialNumber the composite PRIMARY KEY of the AircraftsPilots table and include a Date column.

    Answer: E

  10. You want to create a database with an initial size of 10 Gigabytes, a maximum size of 15 Gigabytes, and a filegrowth of 5 Gigabytes. You also want to include a transaction log file with an initial size of 1 Gigabyte, a maximum size of 2 Gigabytes, and a filegrowth of 1 Gigabyte .Which of the following statements would you use?

    1. CREATE DATABASE MyDatabase
      ON (NAME = ‘MyDatabase_mdf’,
      FILENAME = ‘C:\MyDatabase.mdf’,
      SIZE = 10,
      MAXSIZE = 15,
      FILEGROWTH = 1)

      LOG ON (NAME = ‘MyDatabase.ldf’
      FILENAME = ‘C:\MyDatabase.mdf’,
      SIZE = 1,
      MAXSIZE = 2,
      FILEGROWTH = 1)

    2. CREATE DATABASE MyDatabase
      ON (NAME = ‘MyDatabase_mdf’,
      FILENAME = ‘C:\MyDatabase.mdf’,
      SIZE = 10 GB,
      MAXSIZE = 15 GB,
      FILEGROWTH = 1 GB)

      LOG ON (NAME = ‘MyDatabase.ldf’
      FILENAME = ‘C:\MyDatabase.mdf’,
      SIZE = 1 GB,
      MAXSIZE = 2 GB,
      FILEGROWTH = 1 GB)

    3. CREATE DATABASE MyDatabase
      ON (NAME = ‘MyDatabase_mdf’,
      FILENAME = ‘C:\MyDatabase.mdf’,
      SIZE = 10 Gigabytes,
      MAXSIZE = 15 Gigabytes,
      FILEGROWTH = 1 Gigabytes)

      LOG ON (NAME = ‘MyDatabase.ldf’
      FILENAME = ‘C:\MyDatabase.mdf’,
      SIZE = 1 Gigabytes,
      MAXSIZE = 2 Gigabytes,
      FILEGROWTH = 1 Gigabytes)

    4. CREATE DATABASE MyDatabase
      ON (NAME = ‘MyDatabase_mdf’,
      SIZE = 10 MB,
      MAXSIZE = 15 MB,
      FILEGROWTH = 1 MB)

      LOG ON (NAME = ‘MyDatabase.ldf’
      SIZE = 1 MB,
      MAXSIZE = 2 MB,
      FILEGROWTH = 1 MB)

    Answer: B

Given above are Sample MCSE Questions on SQL 2000 with professional answers. Some more Microsoft Microsoft XP Professional Questions and more help on this technical segment have also been provided in our next entries for your reference.

« Back
Sample Test Questions
GRE Sample Questions
CAT Sample Questions
GMAT Sample Questions
TOEFL Sample Questions
ACT Sample Questions
SAT Sample Questions
LSAT Sample Questions
PSAT Sample Questions
MCAT Sample Questions
PMP Sample Questions
GED Sample Questions
ECDL Sample Questions
DMV Sample Questions
CCNA Sample Questions
MCSE Sample Questions
Network+ Sample Questions
A+ Sample Questions
Technical Sample Questions
WASL Sample Questions
CISA Sample Questions

Other Sample Questions
Sample Interview Questions
Sample Teacher Interview Questions
Sample Citizenship Questions
Accuplacer Sample Questions
Science Bowl sample Questions
Driving Test Sample Questions
Sample Survey Questions Sample Essay Questions
Sample Behavioral Interview Questions

Copyright © 2004-2013, Best BSQ. All Rights Reserved.