Description
Objectives:
The purpose of the first lab of DBS211 is to familiarize yourself with the User Interface, SQL Developer, that we will be using throughout the course to communicate with the Oracle server. By the end of this lab you should be able to:
• Successfully establish a connection with and login to the Oracle database server using SQL Developer
• Run the sample database creation script
• Navigate SQL Developer to view the tables created, their structure and the data contained within them.
LAB 01 – SUBMISSION
Explore the Database
Answer the following questions in the SQL Developer Worksheet area. Use comment blocks for answers that are not running code.
In the connections window, expand Tables.
1) How many tables have been created? List the names of the created tables.
8 tables were created: CUSTOMERS, EMPLOYEES, OFFICES, ORDERDETAILS, ORDERS, PAYMENTS, PRODUCTLINES, PRODUCTS
2) Click on table customers. Click on the Data tab near the top of the worksheet. How many rows are there in the table customers?
122 rows
3) What SQL statement would return the same results. Write the statement in the .sql file and execute it. SELECT count(CUSTOMERNUMBER) FROM customers
You will learn how to select rows and columns from a table by writing SQL select statements later in this course.
4) How many columns does the customers table have? List the column names.
13 columns: CUSTOMERNUMBER, CUSTOMERNAME, CONTACTLASTNAME, CONTACTFIRSTNAME, PHONE, ADDRESSLINE1, ADDRESSLINE2, CITY, STATE, POSTALCODE, COUNTRY, SALESREPEMPLOYEENUMBER, CREDITLIMIT.
5) What is the value of each column in the first row in table customers? Write the column name and the column data type in addition to the value.
CUSTOMERNU MBER
CUSTOMERN
AME
CONTACTLAST
NAME
CONTACTFIRST
NAME PHONE
ADDRESSLI
NE1
ADDRESSLI
NE2
CIT
Y
STA
TE
POSTALC
ODE
COUNT
RY
SALESREPEMPLOYEEN
UMBER
CREDITLI
MIT
103 Atelier graphique Schmitt Carine 40.32.2
555 54, rue
Royale Nantes 440
00 France 1370 21000 103
6) Write the number of rows and columns for the rest of the tables in your schema. Format it something like the following.
Table Name Rows Columns
CUSTOMERS 122 13
EMPLOYEES 23 8
OFFICES 7 9
ORDERDETAILS 0 5
ORDERS 0 7
PAYMENTS 0 4
PRODUCTLINES 7 4
PRODUCTS 110 9
7) Right Click on the orderdetails table and choose tables/count rows. How many rows does the order details table include?
The table ORDERDETAILS contains 0 rows.
8) Write the following SQL statement in the new tab.
desc offices;
You can also write
describe offices;
What is the result of the statement execution?
The given command returns the name of the columns, if it accepts null, and the data type of the table offices.
9) Type the following statements in, execute them, then briefly describe what the statement is doing!
SELECT * FROM employees;
The command returns all rows and columns from the table employees with the default sorting.
SELECT * FROM customer ORDER BY ContactLastName;
This will result in an error. There is no table named customer. If we use customers instead, the command returns all rows and columns from the table customer ordered by ContactLastName.
10) How many constraints does the products table have?
CONSTRAINTS:
• productCode PRIMARY KEY
• it does not accept NULL entrances in any collum
• prod_line_fk FOREIGN KEY (productLine) REFERENCES productlines (productLine)
11) Set the font size in the worksheet editor to a size that is best for you. (Hint: Tools/Preferences) Changed to size 11.
