STRUCTURE QUERY LANGUAGE (SQL)

If you have any queries please leave a message here
Your Message
×


SQL commands are mainly categorized into four categories as discussed below :

1) DDL(Data Definition Language) - DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema.

Examples of DDL commands :


2) DML(Data Manipulation Language) - The SQL commands that deals with the manipulation of data present in database belong to DML or Data Manipulation Language and this includes most of the SQL statements.

Examples of DML commands :


3) DCL(Data Control Language) - DCL includes commands such as GRANT and REVOKE which mainly deals with the rights, permissions and other controls of the database system.

Examples of DCL commands :


4) TCL(transaction Control Language) - TCL commands deals with the transaction within the database.

Examples of TCL commands :




CREATE TABLE

Considered the following relation :

branch(branch_name, branch_city, assets)
customer(customer_id, customer_name, customer_street)
loan(loan_number, branch_name, amount)
borrower(customer_id, loan_number)
account(account_number, branch_name, balance)
depositor(customer_id, account_number)

create table customer
(customer_idchar(10),
customer_namechar(30),
customer_addresschar(50),
primary key (customer_id)) ;

create table branch
(branch_namechar(15),
branch_citychar(30),
assetsinteger,
primary key (branch_name),
check (assets >= 0)) ;

create table account
(account_numberchar(10),
branch_namechar(15),
balanceinteger,
primary key (account_number),
check (balance >= 0)) ;

create table depositor
(customer_idchar(10),
account_numberchar(10),
primary key (customer_id, account_number)) ;


create table student
(student_idchar(10),
student_namechar(30) not null,
degree_levelchar(15),
primary key (student_id),
check (degree-level in (’Bachelors’, ’Masters’, ’Doctorate’))) ;


ADD/REMOVE ATTRIBUTE

We use the alter table command to add/remove attributes to an existing relation. When we add an attribute all tuples in the relation are assigned null as the value for the new attribute.

alter table customer add customer_city char(20) ;
alter table student add student_address char(50) ;

alter table customer drop customer_city ;
alter table student drop student_address ;


DELETE TABLE

To remove a relation (table) from an SQL database, we use the drop table command. The drop table command deletes all information about the dropped relation from the database.

drop table customer ;
drop table student ;






INSERT QUERY






UPDATE QUERY






DELETE QUERY






SELECT QUERY






SET OPERATIONS






AGGREGATE FUNCTIONS

Aggregate functions are functions that take a collection (a set or multiset) of values as input and return a single value. SQL offers five built-in aggregate functions:

The input to sum and avg must be a collection of numbers, but the other operators can operate on collections of nonnumeric data types, such as strings, as well.






NESTED SUBQUERIES

SET MEMBERSHIP



SET COMPARISON

Find the names of all branches that have assets greater than those of at least one branch located in Kolkata.

SELECT DISTINCT t.branch_name FROM branch as t, branch AS s WHERE t.assets > s.assets AND s.branch_city = ’Kolkata’ ;

SQL does, however, offer an alternative style for writing the preceding query. The phrase “greater than at least one” is represented in SQL by > some.

SELECT branch_name FROM branch WHERE assets > SOME (SELECT assets FROM branch WHERE branch_city = ’Kolkata’) ;

SQL also allows < some, <= some, >= some, = some, and <> some comparisons. As an exercise, verify that = some is identical to in, whereas <> some is not the same as not in. The keyword any is synonymous to some in SQL. Early versions of SQL allowed only any.

Now we modify our query slightly. Let us find the names of all branches that have an asset value greater than that of each branch in Kolkata.

SELECT branch_name FROM branch WHERE assets > ALL (SELECT assets FROM branch WHERE branch_city = ’Kolkata’) ;

SQL also allows < all, <= all, >= all, = all, and <> all comparisons. As an exercise, verify that <> all is identical to not in.

Another example of set comparisons - Find the branch that has the highest average balance. Aggregate functions cannot be composed in SQL. Thus, we cannot use max (avg (. . .)).

SELECT branch_name FROM account GROUP BY branch_name HAVING AVG(balance) >= ALL (SELECT AVG(balance) FROM account GROUP BY branch_name) ;



TEST FOR EMPTY RELATIONS

SQL includes a feature for testing whether a subquery has any tuples in its result. The exists construct returns the value true if the argument subquery is nonempty.

We can test for the nonexistence of tuples in a subquery by using the not exists construct.






ASSERTION

Syntax :

CREATE ASSERTION assertion_name CHECK condition;

Example 1

Student (name, address, phone, age, course)

Students are not allow for admission whose age is greater than 25.

CREATE ASSERTION age_check CHECK ( NOT EXISTS (SELECT * FROM Student WHERE age > 25));

Example 2

Employee (emp_id, manager_id, department, salary)

No employee should have a salary greater than his manager.

CREATE ASSERTION salary_check CHECK ( NOT EXISTS (SELECT * FROM Employee AS a WHERE a.salary >= (SELECT salary FROM Employee AS b WHERE a.manager_id=b.emp_id)));






SQLJ

SQLJ is an emerging database programming tool that allows embedding of static SQL statements in Java programs. The SQLJ translator converts Java programs embedded with static SQL statements into pure Java code, which can then be executed through a JDBC driver against the database.

Considered the table :
Student (enrolment, name, phone, prog_code)

EXAMPLE 1

//Import necessary classes
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import java.sql.*;
import java.io.*;
import oracle.sqlj.runtime.*;

public class ViewDetails
{
public static void main (String args[ ]) throws SQLException
{
//Connect to the database
DefaultContext dc = Oracle.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","system",true);
DefaultContext.setDefaultContext(dc);
int s_phone;
String s_name, p_code;
try
{
#sql { SELECT name, phone, prog_code INTO :s_name, :s_phone, :p_code FROM Student WHERE enrolment=105508022 };
}
catch (SQLException e)
{
System.out.println("Invalid symbol");
}
System.out.println("\n"+s_name+"\t"+s_phone+"\t"+p_code);
}
}


EXAMPLE 2

//Import necessary classes
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import java.sql.*;
import java.io.*;
import oracle.sqlj.runtime.*;

public class ViewDetails
{
public static void main (String args[ ]) throws SQLException
{
//Connect to the database
DefaultContext dc = Oracle.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","system",true);
DefaultContext.setDefaultContext(dc);
int enrolment_no, s_phone;
String s_name, p_code;
try
{
#sql result = { SELECT * FROM Student };
#sql { FETCH :result INTO :enrolment_no, :s_name, :s_phone, :p_code };
while ( !result.endFetch( ) )
{
System.out.println("\n"+enrolment_no+"\t"+s_name+"\t"+s_phone+"\t"+p_code);
#sql { FETCH :result INTO :enrolment_no, :s_name, :s_phone, :p_code };
}
}
catch (SQLException e)
{
System.out.println("Invalid symbol");
}
}
}


EXAMPLE 3

//Import necessary classes
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import java.sql.*;
import java.io.*;
import oracle.sqlj.runtime.*;

public class ViewDetails
{
public static void main (String args[ ]) throws SQLException
{
//Connect to the database
DefaultContext dc = Oracle.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","system",true);
DefaultContext.setDefaultContext(dc);
try
{
#sql result = { SELECT * FROM Student };
while ( result.next( ) )
{
int enrolment_no = result.ENROLMENT( );
String s_name = result.NAME( )
int s_phone = result.PHONE( )
String p_code = result.PROG_CODE( )
System.out.println("\n"+enrolment_no+"\t"+s_name+"\t"+s_phone+"\t"+p_code);
}
}
catch (SQLException e)
{
System.out.println("Invalid symbol");
}
}
}






VIEW

A view is a virtual or logical table that allows to view or manipulate parts of the tables. Views are useful for security of data.

Syntax :

CREATE or REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name(s) WHERE condition;

A view can be dropped using a DROP statement as :
DROP VIEW view_name;

There are many advantages using view :

Example :

A student''s database may have the following tables :
student ( name, enrolment_no, date_of_birth )
marks ( enrolment_no, subject_code, marks )

CREATE VIEW subject_performance AS SELECT s.enrolment_no, name, subject_code, marks FROM student AS s, marks AS m WHERE s.enrolment_no=m.enrolment_no AND subject_code='MCS-043' ORDER BY s.enrolment_no;

Once a view has been created, it can be queried exactly like a base table. For example :






GRANT AND REVOKE

SQL GRANT is a command used to provide access or privileges on the database objects to the users.

Syntax :

GRANT < permission >
ON object_name
TO {user_name | PUBLIC | role_name}
[WITH GRANT OPTION];

Example :

Considered the table :
Student (enrolment, name, phone, prog_code)

If you wanted to grant SELECT, INSERT, UPDATE, and DELETE privileges on a table called Student to a user name "user1", you would run the following GRANT statement:

GRANT SELECT, INSERT, UPDATE, DELETE ON Student TO user1;

You can also use the ALL keyword to indicate that you wish ALL permissions to be granted for a user named "user2".

GRANT ALL ON Student TO user2;

If you wanted to grant only SELECT access on your table to all users, you could grant the privileges to the public keyword.

GRANT SELECT ON Student TO public;




Syntax :

REVOKE < permission >
ON object_name
FROM {user_name | PUBLIC | role_name}

Example :

If you wanted to revoke DELETE privileges on a table called Student from a user named "user1", you would run the following REVOKE statement:

REVOKE DELETE ON Student FROM user1;

If you wanted to revoke ALL privileges on a table for a user named anderson, you could use the ALL keyword as follows:

REVOKE ALL ON Student FROM user2;

If you had granted ALL privileges to public (all users) on the Student table and you wanted to revoke these privileges, you could run the following REVOKE statement:

REVOKE ALL ON Student FROM public;

Grant/Revoke Privileges on Functions/Procedures :

When dealing with functions and procedures, you can grant users the ability to EXECUTE these functions and procedures. The syntax for granting EXECUTE privileges on a function/procedure in Oracle is:

GRANT EXECUTE ON object TO user;

Once you have granted EXECUTE privileges on a function or procedure, you may need to REVOKE these privileges from a user. To do this, you can execute a REVOKE command.

REVOKE EXECUTE ON object FROM user;



ABOUT US

QuestionSolves.com is an educational website that helps worldwide students in solving computer education related queries.

Also, different software like Visual Studio, SQL Server, Oracle etc. are available to download in different versions.

Moreover, QuestionSolves.com provides solutions to your questions and assignments also.


MORE TOPIC


Windows Command

UNIX Command

IGNOU Assignment Solution

IGNOU Question Paper Solution

Solutions of Different Questions


WHAT WE DO


Website Devlopment

Training

Home Learning

Provide BCA, MCA Projects

Provide Assignment & Question Paper Solution


CONTACT US


Follow Us