oracle database

 welcome ---------------------------------------------------------------------- CREATE TABLE EMPLOYEE (     ENO NUMBER (4),     ENAME VARCHAR2(50) NOT NULL,     DEPTNO NUMBER (2),     JOBTITLE VARCHAR2(50),     DOB DATE,     SALARY NUMBER(8,2),     COMM NUMBER(8,2),     CONSTRAINT PM_KEY PRIMARY KEY(ENO),     CONSTRAINT CK CHECK (SALARY > 0),     CONSTRAINT CKR CHECK (DEPTNO BETWEEN 10 AND 90) );

 The DQL (Data Query Language) is a subset of SQL used to retrieve data from the database. In essence, DQL focuses on querying data stored in tables, and its most common command is SELECT . 1. DQL Commands The primary command in DQL is: SELECT : Used to retrieve data from one or more tables. DQL allows you to: Specify which columns or rows to retrieve. Filter data using conditions. Sort data. Aggregate and group data. Join data from multiple tables. 2. Syntax Basic Syntax sql SELECT column1, column2, ... FROM table_name WHERE condition ; column1, column2 : Columns to retrieve. table_name : The table to query data from. condition : Filter to retrieve specific rows. 3. Types of Queries A. Retrieve All Columns Retrieve all columns from a table: sql SELECT * FROM employees; B. Retrieve Specific Columns Retrieve only specific columns: sql SELECT emp_id, emp_name, salary FROM employees; C. Apply Filtering with WHERE Retrieve rows based on conditions: sql SELECT emp_id, emp_na...

 The DELETE command in SQL is used to remove rows from a table based on specific conditions. It is part of the Data Manipulation Language (DML) and allows precise control over which rows to delete, unlike the TRUNCATE command, which deletes all rows. 1. Purpose The DELETE command is used to: Remove specific rows matching a condition. Delete all rows from a table while retaining the table structure. 2. Syntax A. Basic Syntax DELETE FROM table_name WHERE condition; table_name : Name of the table from which rows will be deleted. condition : A filter specifying which rows to delete. B. Delete All Rows (Caution) If you want to delete all rows from a table: DELETE FROM table_name; ⚠️ Warning : If no WHERE clause is specified, all rows in the table will be deleted. 3. Examples A. Delete Specific Rows Delete an employee with emp_id = 101 : DELETE FROM employees WHERE emp_id = 101; B. Delete Based on Multiple Conditions Delete employees in department 20 earn...

 The UPDATE command in SQL is used to modify existing records in a table. It allows you to update one or more rows by specifying a condition. If no condition is specified, all rows in the table will be updated (which should be done with caution). 1. Purpose The UPDATE command is used to: Modify specific columns for specific rows. Change multiple columns at once. Perform arithmetic or transformations on existing values. 2. Syntax Basic Syntax UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; table_name : The name of the table to update. column1, column2, ... : Columns to modify. value1, value2, ... : New values for the columns. condition : Filters the rows to update. Without this, all rows are updated. 3. Examples A. Update a Single Column Update the salary of an employee with emp_id = 101 : UPDATE employees SET salary = 60000 WHERE emp_id = 101; B. Update Multiple Columns Update the name and salary of an employee: UPDATE em...

 The INSERT command in SQL is used to add new rows of data to a table. It is one of the most frequently used Data Manipulation Language (DML) commands, enabling users to populate tables with initial or additional data. 1. Purpose The INSERT command is used to: Add a single row to a table. Add multiple rows in one operation. Insert data from another table using a query. 2. Syntax A. Basic Insert Syntax INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); table_name : Name of the table where data will be inserted. column1, column2, ... : List of columns where data will be inserted. value1, value2, ... : Corresponding values for the specified columns. B. Inserting All Columns If all columns are to be populated, you can omit the column list: INSERT INTO table_name VALUES (value1, value2, value3, ...); C. Insert Multiple Rows INSERT ALL INTO table_name (column1, column2) VALUES (value1, value2) INTO table_name (colu...

 The DROP command in Oracle Database is used to permanently delete database objects , such as tables, views, indexes, or even the entire database schema. Once a table or other object is dropped, it is removed from the database and cannot be recovered (unless you have enabled features like Oracle's Recycle Bin). 1. Purpose of DROP Command Completely removes the specified object from the database. Deletes the structure and data of the object. Drops dependent objects like constraints, indexes, and triggers associated with the table. 2. Syntax DROP TABLE table_name [CASCADE CONSTRAINTS] [PURGE]; 3. Parameters table_name : Specifies the name of the table to drop. CASCADE CONSTRAINTS : Drops the table along with all constraints (e.g., foreign key constraints) referencing it. PURGE : Removes the table from the Oracle Recycle Bin, making it unrecoverable. 4. Examples A. Basic Drop Table Deletes the table and all its data. DROP TABLE EMP1; B. Drop Ta...

 The ALTER TABLE command in Oracle Database is used to modify the structure of an existing table . You can use it to add, modify, or drop columns, constraints, or even rename the table itself. 1. Syntax of ALTER TABLE ALTER TABLE table_name [ADD column_definition] [MODIFY column_definition] [DROP COLUMN column_name] [RENAME COLUMN old_column_name TO new_column_name] [ADD CONSTRAINT constraint_definition] [DROP CONSTRAINT constraint_name] [RENAME TO new_table_name]; 2. Key Use Cases of ALTER TABLE A. Adding a Column You can add one or more columns to an existing table. Syntax: ALTER TABLE table_name ADD column_name datatype [constraint]; Example: ALTER TABLE EMP1 ADD phone_number VARCHAR2(15); B. Modifying a Column You can modify the data type, size, or constraints of an existing column. Syntax: ALTER TABLE table_name MODIFY column_name new_datatype [constraint]; Example: ALTER TABLE EMP1 MODIFY NAME VARCHAR2(30) NOT NULL; C. Dropping a Column You...

Here’s a comprehensive guide to the CREATE TABLE command in Oracle Database, including its syntax, options, constraints, and examples. Purpose of CREATE TABLE The CREATE TABLE command is used to define a new table in the database. It specifies the table name, its columns, data types, constraints, and other attributes. Syntax CREATE TABLE table_name ( column1 datatype [constraint], column2 datatype [constraint], ..., [table_constraint] ); Key Components table_name : Name of the table being created. It must be unique in the schema. column_name : Name of a column in the table. datatype : Data type of the column (e.g., NUMBER , VARCHAR2 , DATE , etc.). constraint : Optional constraints applied to columns (e.g., NOT NULL , PRIMARY KEY ). table_constraint : Constraints at the table level (e.g., PRIMARY KEY , FOREIGN KEY ). Supported Data Types in Oracle Common Data Types: Data Type Description Example NUMBER Stores num...