SQL Syntax

Select
SELECT "column_name" FROM "table_name"
Distinct
SELECT DISTINCT "column_name"
FROM "table_name"
Where
SELECT "column_name"
FROM "table_name"
WHERE "condition"

Stored procedures or Local Procedures in sql

Stored procedures or Local Procedures:
CREATE [OR REPLACE] PROCEDURE procedure_name [(argument [{IN | OUT | IN OUT}]
argument_type, ...)] {IS | AS}

Procedure_body;

• Procedure_body is a PL/SQL block, must have at least one statement (can be NULL).
• Creating a procedure is a DDL operation -- so implicit COMMIT is done
• Either IS or AS can be used
• Parameter mode (Ada style of call-by-value or reference): IN (read-only), OUT (write-only value is ignored and NOT allowed at RHS value), IN OUT (read-write), default is IN mode. The argument_type must be unconstrained, e.g. CHAR(20) is not allowed.

sql vs plsql

SQL	PL/SQL
SQL stands for Structured Query Language, which does not have procedural programming capability.	PL/SQL stands for Procedural Structured Query Language. Which have advantage over SQL.
SQL is the language that enables relational database users to communicate with the database in a straightforward manner.	PL/SQL is Oracle's procedural language; it comprises the standard language of SQL and a wide array of commands that enable you to control the execution of SQL statements according to different conditions.

Grant/Revoke Privileges in plsql

Grant Privileges on Tables

You can grant users various privileges to tables. These privileges can be any combination of select, insert, update, delete, references, alter, and index. Below is an explanation of what each privilege means.

Privilege	Description
Select	Ability to query the table with a select statement.
Insert	Ability to add new rows to the table with the insert statement.
Update	Ability to update rows in the table with the update statement.
Delete	Ability to delete rows from the table with the delete statement.
References	Ability to create a constraint that refers to the table.
Alter	Ability to change the table definition with the alter table statement.
Index	Ability to create an index on the table with the create index statement.

Data Dictionary in dbms

Data Dictionary

Data dictionary — metadata — system & object privileges — dictionary structure — ``user'' tables - ``all'' tables — ``dba'' tables — ``v$'' tables — frequently used tables — usage examples — exercises — using the dictionary in PL/SQL programs — optional exercise.

Introduction

This document presents Oracle's data dictionary, also called the system catalogue. The data dictionary is the repository of all the meta-data relevant to the objects stored in the database—and also of information concerning the DBMS itself.

SQL Questions and Answers

1.      To see current user name 

 Sql> show user;

2.      Change SQL prompt name 
 SQL> set sqlprompt “Manimara > “ 
Manimara > 
Manimara >

Datatype in sql

CHAR Datatype

The CHAR datatype stores fixed-length character strings. When you create a table with a CHAR column, you must specify a string length (in bytes or characters) between 1 and 2000 bytes for the CHAR column width. The default is 1 byte. Oracle then guarantees that:

·         When you insert or update a row in the table, the value for the CHAR column has the fixed length.

·         If you give a shorter value, then the value is blank-padded to the fixed length.

·         If a value is too large, Oracle Database returns an error.

Oracle Database compares CHAR values using blank-padded comparison semantics.

SQL interview questions and answers

What is SQL and where does it come from?
Structured Query Language (SQL) is a language that provides an interface to relational database systems. SQL was developed by IBM in the 1970s for use in System R, and is a de facto standard, as well as an ISO and ANSI standard. SQL is often pronounced SEQUEL.
In common usage SQL also encompasses DML (Data Manipulation Language), for INSERTs, UPDATEs, DELETEs and DDL (Data Definition Language), used for creating and modifying tables and other database structures.