Guide to Oracle SQL (Buffalo State University Version)
Maintaining a Database using SQL Statements
Oracle uses the Structured Query Language (SQL) to create and maintain the database and tables. SQL [rovides statements that allow database administrators and users to perform create, read, update, and delete (CRUD) operations on a database. SQL is broken down into four (4) areas: a data query language (DQL), a data definition language (DDL), a data control language (DCL), and a data manipulation language (DML). SQL statements are not case-sensitive. I almost always try to upper case my SQL statements just for readability. I also try to use camel-case fo my database, table, and field names. For example,
After you login to your
bscacad3.buffalostate.edu account, type:
sqlplus to enter the SQL appliaction whetre you can enter SQL statements at the
To use Oracle on your local machine, you need to download Oracle Database Express 18c page. After you install 18c, then you can download and install SQL Developer.
If you are using SQL Developer, then right-click on your connection in the SQL developer Connections window. To create a new connection, click the "plus-sign" icon in the "Oracle Connections" pane. Then, you may have to change the connect type from "Basic" to "TNS".
Then choose Open SQL Worksheet. This is where you can enter SQL statements.
CREATE statement can be used to create a new database or a new table within an existing database if you have permissions that allow you to create a new database.
CREATE DATABASE Statement
CREATE DATABASE databaseName statement can be used to create a new database.
CREATE TABLE Statement
CREATE TABLE tableName statement can be used to create a new table within an existing database.
DROP TABLE Statement
DROP TABLE tableName statement can be used to delete a within an existing database.
In SQL Developer pressing the SHIFT-F4 key displays the table structure.
INSERT statement is used to add a record to a table. String (VARCHAR2) values should be enclosed in single quotes (''). In SQL Developer, if you are entering multiple statements you need to highlight all the statements before clicking the run icon ().
In SQL Developer, click the Commit () icon to apply the results of your
INSERT query to your database.
SELECT statement is used to retreive information from the database.
SELECT Statement To Retreive Records
asterisk (*) is used as a wildcard. It is used to retreive all the fields in the table.
If you click the run script icon () in SQL developer you get a different type of output.
SELECT Statement To Retrieve Certain Fields
You can also name the fields you would like return using the
UPDATE statement can be used to modify an existing record in a table.
DELETE statement can be used to remove an existing record from a table.
TRUNCATE statement is used to remove all the records in a table but keep the strucure (fields) of the table intact.
It is important to comment your SQL code. This helps you when you need to go back and modify your code and it helps other devlopers who will need to maintain you code after you move on to new, more challenging projects. SQL provides two (2) types of comments. single line comments (--) and multiple line comments (/* */).