Sixth Chapter Lesson-4: Database queries, Query Languages and Different operators.

Mizanur Rahman

At the end of this lesson-

  • 1. You will be able to explain different database queries.
  • 2. You will be able to explain different query languages.
  • 2. You will be able to explain different database operators.

 

Query: A query is a request for data or information from a database table or combination of tables.

Types of query:

  • 1.Select Query
  • 2.Parameter Query
  • 3.Crosstab Query
  • 4.Action Query

Select Query: Select query is used to retrieve data from a table by selecting fields. It is the mostly used SQL query.

Parameter Query: A parameter query is one of the simplest and most useful queries that is done by taking input as a parameter from a dialog box, which is then used by the query in an expression or criteria. It is used when you want to create a query that you can use many times, but with a different value each time.

Crosstab Query: A crosstab query summarizes the data from one or more fields that are separated into groups based on one or more fields. Crosstab query is a type of select query. This query calculates a sum, average, or other aggregate function, and then groups the results.

Action Query: An action query is a query that makes changes to or moves many records in just one operation. There are four types of action queries: append, update, make-table, and delete.

  • 1.Append Query– takes the set results of a query and “appends” (or adds) them to an existing table.
  • 2.Delete Query– deletes all records in an underlying table from the set results of a query.
  • 3.Make Table Query– as the name suggests, it creates a table based on the set results of a query.
  • 4.Update Query– allows for one or more field in your table to be updated.

 

Query Language: Query languages are used to make queries in a database. These are some query languages-

  • QUEL (Query Language)
  • QBE (Query By Example)
  • SQL (Structured Query Language)

QUEL: QUEL stands for Query Language. It is a data definition and data manipulation for INGRES. INGRES stands for Interactive Graphics and Retrieval System. INGRES is a relational database management system developed by Michael Stonebraker. QUEL does not support relational algebraic operations such as intersection, minus or union.It is based on tuple calculus and does not support nested sub queries.

QBE: Query by example is a query language used in relational databases that allows users to search for information in tables and fields by providing a simple user interface where the user will be able to input an example of the data that he or she wants to access. QBE was created by Moshe Zloof at IBM in the 1970s in parallel to SQL’s development. It is a graphical query language where users can input commands into a table like conditions and example elements. The principle of QBE is that it is merely an abstraction between the user and the real query that the database system will receive. In the background, the user’s query is transformed into a database manipulation language form such as SQL, and it is this SQL statement that will be executed in the background.

SQL: SQL stands for Structured Query Language. SQL is a standard language for accessing and manipulating databases. SQL was the first commercial language introduced for E.F Codd’s Relational model of database. Today almost all RDBMS(MySql, Oracle, Infomix, Sybase, MS Access) use SQL as the standard database query language. SQL is used to perform all types of data operations in RDBMS.  Although SQL is often described as, and to a great extent is, a declarative language (4GL). It also includes procedural elements. SQL consists of many types of statements, which may be informally classed as sub-languages, commonly:

  • 1.Data Definition Language (DDL)
  • 2.Data Manipulation Language (DML)
  • 3.Transaction Control Language(TCL)
  • 4.Data Control Language (DCL)
  • 5.Data query language (DQL)

 

Types of SQL Command:

DBMS SQL command

Data Definition Language (DDL): This includes changes to the structure of the table like creation of table, altering table, deleting a table etc. All DDL commands are auto-committed. That means it saves all the changes permanently in the database.

Here are some commands that come under DDL:

CREATE- It is used to create a new table in the database. See the following syntax-

CREATE TABLE table_name (column_name data_types[,….]);

ALTER- It is used to alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute. See the following syntax-

ALTER TABLE table_name ADD column_name COLUMN-definition;

DROP- It is used to delete both the structure and record stored in the table. See the following syntax-

DROP TABLE table_name;

TRUNCATE- It is used to delete all the rows from the table and free the space containing the table. See the following syntax-

TRUNCATE TABLE table_name;

RENAME- It is used to rename a table.

 

Data Manipulation Language (DML): DML commands are used for manipulating the data stored in the table and not the table itself. DML commands are not auto-committed. It means changes are not permanent to database, they can be rolled back.

Here are some commands that come under DML:

INSERT – It is used to insert data into the row of a table. See the following syntax-

INSERT INTO table_name(col1, col2, col3,…. col N)  VALUES (value1, value2, value3, …. valueN);

UPDATE- This command is used to update or modify the value of a column in the table. See the following syntax-

UPDATE table_name SET [column_name1= value1,…column_nameN = valueN] [WHERE CONDITION]

DELETE- It is used to remove one or more row from a table. See the following syntax-

DELETE FROM table_name [WHERE condition];

 

Transaction Control Language(TCL): These commands are to keep a check on other commands and their affect on the database. TCL commands can only use with DML commands like INSERT, 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:

COMMIT- It is used to save all the transactions to the database.

COMMIT;

ROLLBACK- Rollback command is used to undo transactions that have not already been saved to the database.

ROLLBACK;

SAVEPOINT- It is used to roll the transaction back to a certain point without rolling back the entire transaction.

SAVEPOINT SAVEPOINT_NAME;

 

Data Control Language (DCL): Data control language are the commands to grant and take back authority from any database user.

Here are some commands that come under DCL:

GRANT- It is used to give user access privileges to a database.

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

REVOKE- It is used to take back permissions from the user.

REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

 

Data query language (DQL): Data query language is used to fetch data from tables based on conditions that we can easily apply. It uses only one command:

SELECT- It is used to select the attribute based on the condition described by WHERE clause.

SELECT column_name–/* FROM table_name WHERE conditions;


Database Operators: An operator is a reserved word or a character used primarily in an SQL statement’s WHERE clause to perform operation(s), such as comparisons and arithmetic operations. Different types of operators-

Arithmetic Operators- You use the arithmetic operators to calculate a value from two or more numbers or to change the sign of a number from positive to negative or vice versa.

Comparison Operators- You use the comparison operators to compare values and return a result that is True, False, or Null.

Logical Operators- You use the logical operators to combine two Boolean values and return a true, false, or null result. Logical operators are also referred to as Boolean operators.

Concatenation Operators- You use the concatenation operators to combine two text values into one.

Special operators- You use the special operators to return a True or False result as described in the following table.

 

Lesson Evaluation-

Knowledge Based Questions:

  • a. What is Query?
  • a. What is query language?
  • a. What is SQL?
  • a. What is DDL?
  • a. What is DML?

Comprehension Based Questions:

  • b. Explain the necessity of queries in database.
  • b. Why SQL is called  the soul of database?
  • b. Why SQL is called very high level language?
  • b. “SQL is a set of some statements” -Explain it.

Creative Questions:

Multiple Choice Questions:

 

Written by,

Spread the love

Leave a Reply

Your email address will not be published. Required fields are marked *