Coding tutorials - Sql*
SQL> show user
SQL> connect peter/chen@waketech
SQL> show user
SQL> select * from tab;
SQL> disconnect
SQL> exit
=========
SQL> SHOW USER
SQL> SELECT * FROM emp;
SQL> SELECT * FROM dept;
SQL> SELECT * FROM salgrade;
SQL> SET LINESIZE 150
SQL> SET PAGESIZE 100
SQL> SHOW LINESIZE
SQL> SHOW PAGESIZE
SQL> SELECT * FROM emp;
SQL> LIST
Or
SQL> L
SQL> SELECT * FROM dept;
SQL> SELECT * FROM salgrade;
SQL> ED
=========
SQL> CREATE TABLE test(
empno NUMBER,
sal NUMBER);
SQL> INSERT INTO test VALUES(1000, 5000.333);
SQL> INSERT INTO test VALUES(1000, .333);
SQL> INSERT INTO test VALUES(1000, 500.333);
SQL> SELECT *
FROM test;
=========
o ALTER
o DROP
o TRUNCATE
a. CREATE It is used to create a new table in the database.
Syntax:
CREATE TABLE TABLE_NAME
(
COLUMN_NAME1 DATATYPES(size)s
COLUMN_NAME2 DATATYPES(size)s
--------------
COLUMN_NAMEN DATATYPES(size)s
);
Example:
CREATE TABLE EMP
(
EMPNo VARCHAR2(20)s
EName VARCHAR2(20)s
Job VARCHAR2(20)s
DOB DATE
);
b. DROP : This statement is used to drop an existing database. When you use this statement,
complete information present in the database will be lost.
Syntax
DROP DATABASE DatabaseName;
Example
DROP DATABASE Employee;
The ‘DROP TABLE’ Statement
This statement is used to drop an existing table. When you use this statement, complete
information present in the table will be lost.
Syntax
DROP TABLE TableName;
Example
DROP Table Emp;
c. ALTER
This command is used to delete, modify or add constraints or columns in an existing
table.
The ‘ALTER TABLE’ Statement
This statement is used to add, delete, modify columns in an existing table.
The ‘ALTER TABLE’ Statement with ADD/DROP COLUMN
The ‘ALTER TABLE’ Statement with ADD/DROP COLUMN
You can use the ALTER TABLE statement with ADD/DROP Column command
according to your need. If you wish to add a column, then you will use the ADD
command, and if you wish to delete a column, then you will use the DROP COLUMN
command.
Syntax
ALTER TABLE TableName ADD ColumnName Datatype;
ALTER TABLE TableName DROP COLUMN ColumnName;
Example
--ADD Column MobNo:
ALTER TABLE Emp ADD MobNo Number(10);
--DROP Column MobNo:
ALTER TABLE Emp DROP COLUMN MobNo ;
The ‘ALTER TABLE’ Statement with ALTER/MODIFY COLUMN
This statement is used to change the datatype of an existing column in a table.
Syntax
ALTER TABLE TableName ADD COLUMN ColumnName Datatype;
Example
--Add a column DOB and change the data type to Date.
ALTER TABLE Emp ADD DOB date;
d. TRUNCATE
This command is used to delete the information present in the table but does not delete
the table. So, once you use this command, your information will be lost, but not the
table.
Syntax:
TRUNCATE TABLE table_name;
Example:
TRUNCATE TABLE EMPLOYEE;
2. Data Manipulation Language
o DML commands are used to modify the database. It is responsible for all form
of changes in the database.
o The command of DML is not auto-committed that means it can't permanently
save all the changes in the database. They can be rollbacs.
Here are some commands that come under DML:
o INSERT
o UPDATE
o DELETE
a. INSERT: The INSERT statement is a SQL query. It is used to insert data
into the row of a table.
Syntax:
INSERT INTO TABLE_NAME
(col1s col2s col3s.... col N)
VALUES (value1s value2s value3s .... valueN);
Or
INSERT INTO TABLE_NAME
VALUES (value1s value2s value3s .... valueN);
For example:
INSERT INTO EMP(ENamesJob) VALUES ("SCOTT"s "MANAGER");
b. UPDATE: This command is used to update or modify the value of a
column in the table.
Syntax:
UPDATE table_name SET column1= values column2= values
columnN = value WHERE CONDITION;
For example:
UPDATE Emp SET Ename = 'SMITH' WHERE EmpNo = '1003';
c. DELETE: It is used to remove one or more row from a table.
3. Data Control Language
DCL commands are used to grant and tase bacs authority from any database
user.
Here are some commands that come under DCL:
o Grant
o Revose
a. Grant: It is used to give user access privileges to a database.
Example
GRANT SELECTs UPDATE ON MY_TABLE TO SOME_USERs ANOTHER_USER;
b. Revoke: It is used to tase bacs permissions from the user.
Example
REVOKE SELECTs UPDATE ON MY_TABLE FROM USER1s USER2;
4. Transaction Control Language
TCL commands can only use with DML commands lise INSERTs DELETE and
UPDATE only.
These operations are automatically committed in the database that's why
they cannot be used while creating tables or dropping them.
Here are some commands that come under TCL:
o COMMIT
o ROLLBACK
o SAVEPOINT
a. Commit: Commit command is used to save all the transactions to the
database.
Syntax:
COMMIT;
Example:
DELETE FROM CUSTOMERS WHERE AGE = 25;
COMMIT;
b. Rollback: Rollbacs command is used to undo transactions that have not
already been saved to the database.
Syntax:
ROLLBACK;
Example:
DELETE FROM CUSTOMERS WHERE AGE = 25;
ROLLBACK;
c. SAVEPOINT: It is used to roll the transaction bacs to a certain point
without rolling bacs the entire transaction.
Syntax:
SAVEPOINT SAVEPOINT_NAME;
5. Data Query Language
DQL is used to fetch the data from the database.
SELECT
This statement is used to select data from a database and the data returned is stored in
a result table, called the result-set.
Syntax
SELECT Column1, Column2, ...ColumN FROM TableName;
--(*) is used to select all from the table
SELECT * FROM table_name;
-- To select the number of records to return use:
SELECT TOP 3 * FROM TableName;
Apart from just using the SELECT keyword individually, you can use the following
keywords with the SELECT statement:
o
o DISTINCT
o ORDER BY
o GROUP BY
o HAVING Clause
o INTO
The ‘SELECT DISTINCT’ Statement
This statement is used to return only different values.
Syntax
SELECT DISTINCT Column1, Column2, ...ColumnN FROM TableName;
SELECT DISTINCT MobNo FROM Emp;
Example
The ‘ORDER BY’ Statement
The ‘ORDER BY’ statement is used to sort the required results in ascending or
descending order. The results are sorted in ascending order by default. Yet, if you wish
to get the required results in descending order, you have to use the DESC keyword.
Syntax
SELECT Column1, Column2, ...ColumnN FROM TableName
ORDER BY Column1, Column2, ... ASC|DESC;
Example
Select all employees from the 'Emp’ table sorted by EmpNo:
SELECT * FROM Emp ORDER BY EmpNo;
-- Select all employees from the 'Emp table sorted by EmpNo in Descending order:
SELECT * FROM Employee_Info ORDER BY EmpNo DESC;
Oracle
SQL (Structured Query Language) is a powerful database language used to manage
and manipulate Oracle databases. Here are some key concepts and examples to
help you get started:
### Basic
SQL Operations
#### 1.
**Creating a Table**
```sql
CREATE
TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
email VARCHAR2(100) UNIQUE,
hire_date DATE,
job_id NUMBER,
salary NUMBER
);
```
#### 2.
**Inserting Data**
```sql
INSERT
INTO employees (employee_id, first_name, last_name, email, hire_date, job_id,
salary)
VALUES
(1, 'John', 'Doe', 'john.doe@example.com', TO_DATE('2023-01-15', 'YYYY-MM-DD'),
101, 60000);
```
#### 3.
**Querying Data**
```sql
SELECT *
FROM employees;
SELECT
first_name, last_name, salary
FROM
employees
WHERE
salary > 50000;
```
#### 4.
**Updating Data**
```sql
UPDATE
employees
SET
salary = 65000
WHERE
employee_id = 1;
```
#### 5.
**Deleting Data**
```sql
DELETE
FROM employees
WHERE
employee_id = 1;
```
###
Advanced SQL Operations
#### 1.
**Joining Tables**
Assuming
you have another table called `departments`:
```sql
SELECT
e.first_name, e.last_name, d.department_name
FROM
employees e
JOIN
departments d ON e.department_id = d.department_id;
```
#### 2.
**Using Aggregate Functions**
```sql
SELECT
department_id, AVG(salary) AS average_salary
FROM
employees
GROUP BY
department_id;
```
#### 3.
**Subqueries**
```sql
SELECT
first_name, last_name
FROM
employees
WHERE
salary > (SELECT AVG(salary) FROM employees);
```
#### 4.
**Creating a View**
```sql
CREATE
VIEW high_earners AS
SELECT
first_name, last_name, salary
FROM
employees
WHERE
salary > 50000;
```
#### 5.
**Using PL/SQL for Procedural Logic**
PL/SQL is
Oracle's procedural extension for SQL. Here’s a simple example of a PL/SQL
block:
```sql
DECLARE
v_total_salary NUMBER;
BEGIN
SELECT SUM(salary) INTO v_total_salary FROM
employees;
DBMS_OUTPUT.PUT_LINE('Total Salary: ' ||
v_total_salary);
END;
```
### Tips
for Writing Efficient SQL in Oracle
1. **Use
Indexes**: Indexes can significantly speed up queries. Make sure to index
columns that are frequently used in WHERE clauses or JOIN conditions.
```sql
CREATE INDEX idx_employee_last_name ON
employees(last_name);
```
2.
**Avoid Using `SELECT *`**: Specify the columns you need instead of using
`SELECT *`.
3. **Use
Bind Variables**: Bind variables can improve performance by reusing execution
plans.
4.
**Optimize Joins**: Use appropriate join types and conditions. Prefer inner
joins over outer joins when possible.
5.
**Analyze Execution Plans**: Use the `EXPLAIN PLAN` statement to understand how
Oracle executes your query and identify potential bottlenecks.
```sql
EXPLAIN PLAN FOR
SELECT first_name, last_name FROM employees
WHERE salary > 50000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
```
Oracle
SQL is a powerful tool for managing data, and mastering it can greatly enhance
your ability to work with Oracle databases efficiently.
====
SQL*Plus
is a command-line tool that provides an interface to interact with Oracle
Database. It allows users to execute SQL, PL/SQL, and SQL*Plus commands. Here
are some commonly used SQL*Plus commands and their functionalities:
###
Starting SQL*Plus
```bash
sqlplus
username/password@database
```
###
SQL*Plus Commands
#### 1.
**Connecting to a Database**
```sql
CONNECT
username/password@database
```
#### 2.
**Exiting SQL*Plus**
```sql
EXIT
```
#### 3.
**Executing SQL Scripts**
```sql
START
script_name.sql
@script_name.sql
```
#### 4.
**Formatting Output**
-
**Setting Column Widths:**
```sql
COLUMN column_name FORMAT A30
```
-
**Setting Page Size:**
```sql
SET PAGESIZE 50
```
-
**Setting Line Size:**
```sql
SET LINESIZE 100
```
-
**Suppressing Output:**
```sql
SET FEEDBACK OFF
```
-
**Displaying Column Names Only Once:**
```sql
SET HEADING ON
```
#### 5.
**Spooling Output to a File**
```sql
SPOOL
output_file.txt
-- Your
SQL commands
SPOOL OFF
```
#### 6.
**Displaying the Current User**
```sql
SHOW USER
```
#### 7.
**Describing Database Objects**
```sql
DESC
table_name
```
#### 8.
**Setting SQL*Plus Variables**
-
**Defining Variables:**
```sql
DEFINE variable_name = value
```
- **Using
Variables:**
```sql
SELECT &variable_name FROM dual;
```
-
**Unsetting Variables:**
```sql
UNDEFINE variable_name
```
#### 9.
**Environment Settings**
-
**Setting the SQL Prompt:**
```sql
SET SQLPROMPT 'SQL> '
```
-
**Enabling and Disabling Autocommit:**
```sql
SET AUTOCOMMIT ON
SET AUTOCOMMIT OFF
```
-
**Setting Timing for Commands:**
```sql
SET TIMING ON
```
#### 10.
**Executing PL/SQL Blocks**
```sql
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, World!');
END;
/
```
###
Example Session
Here's an
example of a typical SQL*Plus session:
```bash
$ sqlplus
scott/tiger@orcl
SQL*Plus:
Release 19.0.0.0.0 - Production on Wed Aug 9 10:34:56 2023
Connected
to:
Oracle
Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version
19.3.0.0.0
SQL>
SET LINESIZE 150
SQL>
SET PAGESIZE 50
SQL>
COLUMN last_name FORMAT A20
SQL>
SPOOL employee_report.txt
SQL>
SELECT employee_id, first_name, last_name, salary
2 FROM
employees
3
WHERE salary > 50000
4
ORDER BY last_name;
EMPLOYEE_ID
FIRST_NAME LAST_NAME SALARY
-----------
-------------------- -------------------- ----------
1 John Doe 60000
2 Jane Smith 70000
SQL>
SPOOL OFF
SQL>
EXIT
```
###
Helpful Tips
1. **Use
Scripts**: Store frequently used commands in scripts and execute them with the
`@` or `START` command.
2. **Use
Aliases**: Create aliases for frequently used SQL statements to save time.
3.
**Customize Environment**: Use the SQL*Plus environment settings to customize
your session for better readability and efficiency.
4.
**Check Error Messages**: Always check for error messages after running
commands to ensure they executed successfully.
SQL*Plus
is a powerful tool for database administrators and developers to interact with
Oracle Database. Mastering its commands can greatly enhance your productivity
and efficiency.
.png)
.png)