Sql Code Masterz

Cursors in SQL procedures In SQL procedures, a cursor make it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. By using the same mechanics, an SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application. A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed. To use cursors in SQL procedures, you need to do the following: Declare a cursor that defines a result set. Open the cursor to establish the result set. Fetch the data into local variables as needed from the cursor, one row at a time. Close the cursor when done To work with cursors you must use the following SQL statements: DECLARE CURSOR OPEN FETCH CLOSE The following example demonstrates the basic use of a read-only cursor within an SQL procedure: CREATE PROCEDURE sum_salaries(OUT su...

SQL Views A VIEW is a virtual table, through which a selective portion of the data from one or more tables can be seen. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity. A view is stored as a SELECT statement in the database. DML operations on a view like INSERT, UPDATE, DELETE affects the data in the original table upon which the view is based. The Syntax to create a sql view is CREATE VIEW view_name AS SELECT column_list FROM table_name [WHERE condition]; view_name is the name of the VIEW. The SELECT statement is used to define the columns and rows that you want to display in the view. For Example: to create a view on the product table the sql query would be like CREATE VIEW view_product AS SELECT product_id, product_name FROM product;  Source : -http://beginner-sql-tutorial.com/sql-views.htm

SQL Index Index in sql is created on existing tables to retrieve the rows quickly. When there are thousands of records in a table, retrieving information will take a long time. Therefore indexes are created on columns which are accessed frequently, so that the information can be retrieved quickly. Indexes can be created on a single column or a group of columns. When a index is created, it first sorts the data and then it assigns a ROWID for each row. Syntax to create Index: CREATE INDEX index_name ON table_name (column_name1,column_name2...); Syntax to create SQL unique Index: CREATE UNIQUE INDEX index_name ON table_name (column_name1,column_name2...); index_name is the name of the INDEX. table_name is the name of the table to which the indexed column belongs. column_name1, column_name2.. is the list of columns which make up the INDEX. In Oracle there are two types of SQL index namely, implicit and explicit. Implicit Indexes: They are created when a column i...

SQL Alias SQL Aliases are defined for columns and tables. Basically aliases is created to make the column selected more readable. For Example: To select the first name of all the students, the query would be like: Aliases for columns: SELECT first_name AS Name FROM student_details; or SELECT first_name Name FROM student_details; In the above query, the column first_name is given a alias as 'name'. So when the result is displayed the column name appears as 'Name' instead of 'first_name'. Output: Name ------------- Rahul Sharma Anjali Bhagwat Stephen Fleming Shekar Gowda Priya Chandra Aliases for tables: SELECT s.first_name FROM student_details s; In the above query, alias 's' is defined for the table student_details and the column first_name is selected from the table. Aliases is more useful when There are more than one tables involved in a query, Functions are used in the query, The column names are big or not readabl...

DCL commands are used to enforce database security in a multiple user database environment. Two types of DCL commands are GRANT and REVOTE. Only Database Administrator's or owner's of the database object can provide/remove privileges on a databse object. SQL GRANT Command SQL GRANT is a command used to provide access or privileges on the database objects to the users. The Syntax for the GRANT command is: GRANT privilege_name ON object_name TO {user_name |PUBLIC |role_name} [WITH GRANT OPTION]; privilege_name is the access right or privilege granted to the user. Some of the access rights are ALL, EXECUTE, and SELECT. object_name is the name of an database object like TABLE, VIEW, STORED PROC and SEQUENCE. user_name is the name of the user to whom an access right is being granted. user_name is the name of the user to whom an access right is being granted. PUBLIC is used to grant access rights to all users. ROLES are a set of privileges grouped together. ...

SQL Integrity Constraints Integrity Constraints are used to apply business rules for the database tables. The constraints available in SQL are Foreign Key, Not Null, Unique, Check. Constraints can be defined in two ways 1) The constraints can be specified immediately after the column definition. This is called column-level definition. 2) The constraints can be specified after all the columns are defined. This is called table-level definition. 1) SQL Primary key: This constraint defines a column or combination of columns which uniquely identifies each row in the table. Syntax to define a Primary key at column level: column name datatype [CONSTRAINT constraint_name] PRIMARY KEY Syntax to define a Primary key at table level: [CONSTRAINT constraint_name] PRIMARY KEY (column_name1,column_name2,..) column_name1, column_name2 are the names of the columns which define the primary Key. The syntax within the bracket i.e. [CONSTRAINT constraint_name] is optional. For Example:...

SQL Tuning/SQL Optimization Techniques: 1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'. For Example: Write the query as SELECT id, first_name, last_name, age, subject FROM student_details; Instead of: SELECT * FROM student_details; 2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes. For Example: Write the query as SELECT subject, count(subject) FROM student_details WHERE subject != 'Science' AND subject != 'Maths' GROUP BY subject; Instead of: SELECT subject, count(subject) FROM student_details GROUP BY subject HAVING subject!= 'Vancouver' AND subject!= 'Toronto'; 3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query. For Example: Write the query as SELECT name FROM employee ...