Review Basic SQL Commands and Use of the Data Dictionary Tables
COSC 304
Lab 1 – Basic SQL
September 10, 2012
This Lab assumes familiarity with the SQL*Plus tool. If you are not familiar with this tool, you should review COSC 126 Labs 1 and 2. During this lab you should log in to the SQL*Plus tool and follow along with the provided examples.
Objective
Review basic SQL commands and use of the data dictionary tables
- Creating a Table. To describe the full syntax of the create table command is beyond the scope of this lab and also beyond the scope of this course. Instead we will go over several, hopefully the most useful, forms of this command by means of examples.
In its most simple form, to create a table you need to supply a table name and names of table columns with column datatypes. Here is an example:
SQL> create table phones
2(fname varchar2(8),phone number);
Table created.
SQL, as well as SQL*Plus is not case sensitive; I could have typed this command in uppercase letters or mixture of uppercase and lowercase.It would make no difference. Names of tables and columns (and other database objects) are always stored in uppercase, so in the data dictionary, the name of my table is PHONES. The table has two columns FNAME and PHONE. The FNAME columns can store strings of up to 8 characters long and PHONE column can store numbers.
Oracle Datatypes. The Oracle version of SQL has two datatypes for storing strings: VARCHAR2 for variable length strings and CHAR for strings of fixed length. When in doubt always use VARCHAR2; use the CHAR datatype only in situations where strings will have a constant number of characters (e.g. postal code or province abbreviations). You can store strings of up to 4000 characters in a VARCHAR2 column. The data type LONG can store up to 2 gigabytes of text, but it is subject to several restrictions. For example you cannot have more than one column of type LONG in a table. NUMBER(p, s) is a number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127. If you omit precision and scale, NUMBER is a floating-point number with decimal precision 38. This is the most general number datatype, we will use it most of the time. The DATE datatype stores date and time information. DATE columns stores the following information: century, year, month, day, hour, minute, and second. To specify a date value, you must convert a character or numeric value to a date value with the TO_DATE function. If you specify a date value without a time component, the default time is 12:00:00 AM (midnight).
Most of the time we will be using 3 datatypes: VARCHAR2, NUMBER and DATE. The DATE datatype will be explained in the future labs.
Now, that we created a table, how can you verify that you actually have this table in your schema? In Windows, when you create a file, you can confirm its existence, and find some information about the file using Windows Explorer (or My Computer). Databases store data about database objects in Data Dictionary tables. In Oracle, the data dictionary tables that store information about user’s objects (objects which belong to the user’s schema) have prefix USER_. For example: USER_TABLES, USER_OBJECTS, USER_VIEWS, USER_SYNONYMS etc. Tables with prefix ALL_ store information about objects belonging to other users. For example, you can use table ALL_TABLES to find all tables in the database to which you have access.
Data Dictionary Tables. One of the most important tables is the USER_TABLES table. Use desc (describe) command to see what columns are in this table.
As you can see there are quite a few columns (over 40), so if you want to list tables in your schema do not select everything from this table (select * from user_tables). In this lab we are interested in two columns: TABLE_NAME, and TABLESPACE_NAME:
SQL> select table_name, tablespace_name
2 from user_tables;
TABLE_NAME TABLESPACE_NAME
------
PHONES USERS
I have one table in my schema. It is stored in tablespaceUSERS. Tablespace is a logical database storage structure. It is like a virtual drive on a computer. Physically, each tablespace is stored on one or more operating system datafiles. But, database users do not refer to physical datafiles, instead they refer to a logical storage structure called a tablespace. In order to create tables, database user must have storage space on one or more tablespaces. The data dictionary table which stores tablespace quotas is called USER_TS_QUOTAS. Use the desc command to see what the columns are in this table:
SQL> desc user_ts_quotas
Name Null? Type
------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BYTES NUMBER
MAX_BYTES NUMBER
BLOCK NOT NULL NUMBER
MAX_BLOCKS NUMBER
Max_bytes is the number of bytes allocated (your quota) and bytes is the amount of storage used. I prefer to see storage in megabytes, so I divide this numbers by 1024*1024:
SQL> select tablespace_name,
2 bytes/1024/1024, max_bytes/1024/1024
3 from user_ts_quotas;
TABLESPACE_NAME BYTES/1024/1024 MAX_BYTES/1024/1024
------
USERS .5 3
USERS2 0 2
My quota on USERS is 3 mbytes and 2 mbyteson USERS2. So far I have used half a megabyte on USERS.Do you have storage quotas on USERS2?
The PHONE table was created on USERS because USERS is your default tablespace. Information about users is stored in the ALL_USERS and USER_USERS dictionary tables. Use the desc command to see what columns are in these tables. Select default_tablespace, created and expiry_date. You can see that USERS is your default tablespace. Created is the date when your schema was created. Expiry date is NULL, there is no date set after which your account will expire.
How can you create a table on a tablespace of your choice?
- Creating a Table on a specific Tablespace. HIf you had privileges on the USER2 tablespace and a non-empty quota, here is how you would create a table on USERS2:
SQL> create table phones2
2 (fname varchar2(8), phone number)
3 tablespace users2;
What error do you get if you try to create this table? Create the phones2 table on the USERS tablespace.
- Inserting values into a table. There are two basic formats of theinsert command:
- insert into table_name values (list of values)
- insert into table_name select statement
Below are examples of the use of the first format:
SQL> insert into phones values
2 ('Tom',8604545);
1 row created.
now I can retype line 2 in the buffer and execute it again
SQL> 2 ('Ken',2313434)
SQL> /
1 row created.
if I was to enter many rows I would use a command with a prompt:
SQL> 2 ('&fname',&phone)
SQL> /
Enter value for fname: Pam
Enter value for phone: 4568989
old 2: ('&fname',&phone)
new 2: ('Pam',4568989)
1 row created.
SQL> /
Enter value for fname: Tim
Enter value for phone: 6768080
old 2: ('&fname',&phone)
new 2: ('Tim',6768080)
Now select all the rows from phones to see the content of the table:
SQL> select * from phones;
FNAME PHONE
------
Tom 8604545
Ken 2313434
Pam 4568989
Tim 6768080
Inserting values into a table using a select statement. We can insert rows from the phones table into phones2 table using select statement. In the following example we will insert rows where first name starts with T:
SQL> insert into phones2
2 select * from phones
3 where fname like 'T%';
2 rows created.
You should experiment with this format of insert statement. We will have more examples later on in this lab.
Dual table. The select statement that you are familiar with has the following format:
select list of columns
from list of tables
where condition
The select clause of this statement lists columns from a table (or tables) that we wish to retrieve and thewhere clause specifies the condition that retrieved rows must satisfy.
The select clause can include constant values (literals) and operations (arithmetic or string) on column values and constants. The following example may not make much sense but it illustrates the point:
SQL> select 'Fname = ', fname, phone*4, '4*23=', 4*23
2 from phones;
'FNAME=' FNAME PHONE*4 '4*23 4*23
------
Fname = Tom 34418180 4*23= 92
Fname = Ken 9253736 4*23= 92
Fname = Pam 18275956 4*23= 92
Fname = Tim 27072320 4*23= 92
I could use aselect statement to calculate 1234*4321 without using any columns from phone table:
SQL> select 1234*4321
2 from phones;
1234*4321
------
5332114
5332114
5332114
5332114
Of course for this purpose it would make more sense to use a table with just one row instead of four. You could create your own table with one row or you can use the table DUAL that is supplied with every Oracle database specifically for this purpose.
SQL> select 1234*4321
2 from dual;
1234*4321
------
5332114
- Creating a Table “on the fly”. Suppose that during a lab exam you are given the following question:
Create an appropriate Grades table and store the following row:
Name / Course / GradeMary Popkins / Database Systems / 97
You can do it in two steps, first create the table and then insert values or you can use a select statement to create this table “on the fly”:
SQL> create table grades
2 as
3 select 'Mary Popkins' Name, 'Database Systems' Course,
4 97 grade
5 from dual;
Table created.
Use thedesc command to see what the datatypes of the columns are.
SQL> desc grades
Name Null? Type
------
NAME CHAR(12)
COURSE CHAR(16)
GRADE NUMBER
You can see that columns NAME and COURSE got theCHAR datatype which is not what we would hope for. In general you cannot change the datatype of a column which has values in it. But you can increase the size of alphanumeric columns and you can change it from char to varchar2 and vice versa. The command used to change table definitions is ALTERTABLE, we will use it in the future labs.
Here is another example of creating a table using select statement. Suppose that I want to change order of columns and decrease the phone number by 100 (do not ask me why, this is just an example of what you can do):
SQL> create table phones3
2 as
3 select phone-100 phone, fname
4 from phones;
Table created.
An SQL select statement can be stored in the database. A stored select statement is called a VIEW. The syntax to create a view is the same as the syntax to create a table using theselect statement:
SQL> create view phones_view
2 as
3 select phone-100 phone, fname
4 from phones;
View created.
Select all rows from phones3 and select all rows from phones_view, as you can see there is no difference. Now delete all rows from the phones table where fname starts with T:
SQL> delete from phones
2 where fname like 'T%';
2 rows deleted.
Select again from phones3 and phone_view. Now there is a difference, view is a stored select statement therefore it reflects changes made to the base table phones, where phones3 is a table which has no ties to the table phones.
- Data Access Control. Can other users of the database see your tables? In general, no. Only a user with DBA (Data Base Administration) system privileges can. Other users need your permission. For example, to let user DS_AKEN read my phones table, I have to grant (give) this user select privileges on table phones. Here is the command:
SQL> grant select on phones to ds_aken;
Grant succeeded.
SQL>
The following statement will allow all users to select from my table:
SQL> grant select on phones to public;
Grant succeeded.
SQL>
Command to revoke this privilege is:
SQL> revoke select on phones from public;
Revoke succeeded.
SQL>
Notice that ds_aken still can select from phones. Ds_aken’s privilege has not been revoked.
- Transaction Control Commands. Changes to the data in the database are not permanent until they are committed. Changes to the database structure do not need to be committed. They take place immediately after a data definition command has been issued. It is important to distinguish between SQL commands that manipulate data (DML commands) and SQL commands that define or change database structures (DDL commands). For example, commands insert, update, delete are DML command. create table and create view are examples of DDL commands.
The command to commit changes (make changes permanent) is
SQL> commit;
Commit complete.
- Any changes that have not been committed can be undone using therollback command:
SQL> rollback;
Rollback complete.
Changes to the data can be committed explicitly (by issuing commit command) or implicitly. Each time you exit the SQL*Plus window using the exit command there is an implicit commit. In other words you do not have to worry about make changes permanent, SQL*Plus does it for you. Each time you issue a DDL command, there is an implicit commit. For example, if you delete 5 rows from table T and then create another table, this deletion cannot be undone.
All the commands between the last commit (implicit or explicit) and the next commit constitute a database transaction. Transactions are units of work on a database. A consistent database will never store part of a transaction (either all of it or nothing).
There is nothing to hand in from this lab assignment, but make sure to complete all exercises in this lab.
Lab Assignment
I will verify that you have created tables: phones, phones2, grades and the view,phones_view.
COSC 304 Lab 1Page 1