Which of the following language is structured query language?
Structured Query Language is a standard Database language which is used to create, maintain and retrieve the relational database. Following are some interesting facts about SQL. Show . What is Relational Database? Relational database means the data is stored as well as retrieved in the form of relations (tables). Table 1 shows the relational database with only one relation called STUDENT which stores ROLL_NO, NAME, ADDRESS, PHONE and AGE of students. STUDENT
TABLE 1 These are some important terminologies that are used in terms of relation. Attribute: Attributes are the properties that define a relation. e.g.; ROLL_NO, NAME etc. Tuple: Each row in the relation is known as tuple. The above relation contains 4 tuples, one of which is shown as:
Degree: The number of attributes in the relation is known as degree of the relation. The STUDENT relation defined above has degree 5. Cardinality: The number of tuples in a relation is known as cardinality. The STUDENT relation defined above has cardinality 4. Column: Column represents the set of values for a particular attribute. The column ROLL_NO is extracted from relation STUDENT. The queries to deal with relational database can be categories as: Data Definition Language: It is used to define the structure of the database. e.g; CREATE TABLE, ADD COLUMN, DROP COLUMN and so on. Data Manipulation Language: It is used to manipulate data in the relations. e.g.; INSERT, DELETE, UPDATE and so on. Data Query Language: It is used to extract the data from the relations. e.g.; SELECT So first we will consider the Data Query Language. A generic query to retrieve from a relational database is:
Part of the query represented by statement 1 is compulsory if you want to retrieve from a relational database. The statements written inside [] are optional. We will look at the possible query combination on relation shown in Table 1. Case 1: If we want to retrieve attributes ROLL_NO and NAME of all students, the query will be: SELECT ROLL_NO, NAME FROM STUDENT;
Case 2: If we want to retrieve ROLL_NO and NAME of the students whose ROLL_NO is greater than 2, the query will be: SELECT ROLL_NO, NAME FROM STUDENT WHERE ROLL_NO>2; CASE 3: If we want to retrieve all attributes of students, we can write * in place of writing all attributes as: SELECT * FROM STUDENT WHERE ROLL_NO>2;
CASE 4: If we want to represent the relation in ascending order by AGE, we can use ORDER BY clause as: SELECT * FROM STUDENT ORDER BY AGE;
Note: ORDER BY AGE is equivalent to ORDER BY AGE ASC. If we want to retrieve the results in descending order of AGE, we can use ORDER BY AGE DESC. CASE 5: If we want to retrieve distinct values of an attribute or group of attribute, DISTINCT is used as in: SELECT DISTINCT ADDRESS FROM STUDENT;
If DISTINCT is not used, DELHI will be repeated twice in result set. Before understanding GROUP BY and HAVING, we need to understand aggregations functions in SQL. AGGRATION FUNCTIONS: Aggregation functions are used to perform mathematical operations on data values of a relation. Some of the common aggregation functions used in SQL are:
SELECT COUNT (PHONE) FROM STUDENT;
SELECT SUM (AGE) FROM STUDENT; In the same way, MIN, MAX and AVG can be used. As we have seen above, all aggregation functions return only 1 row. AVERAGE: It gives the average values of the tupples. It is also defined as sum divided by count
values. MAXIMUM:It extracts the maximum value among the set of tupples. MINIMUM:It extracts the minimum value amongst the set of all the tupples. GROUP BY: Group by is used to group the tuples of a relation based on an attribute or group of attribute. It is always combined with aggregation function which is computed on group. e.g.; SELECT ADDRESS, SUM(AGE) FROM STUDENT GROUP BY (ADDRESS); In this query, SUM(AGE) will be computed but not for entire table but for each address. i.e.; sum of AGE for address DELHI(18+18=36) and similarly for other address as well. The output is:
If we try to execute the query given below, it will result in error because although we have computed SUM(AGE) for each address, there are more than 1 ROLL_NO for each address we have grouped. So it can’t be displayed in result set. We need to use aggregate functions on columns after SELECT statement to make sense of the resulting set whenever we are using GROUP BY. SELECT ROLL_NO, ADDRESS, SUM(AGE) FROM STUDENT GROUP BY (ADDRESS); NOTE: An attribute which is not a part of GROUP BY clause can’t be
used for selection. Any attribute which is part of GROUP BY CLAUSE can be used for selection but it is not mandatory. But we could use attributes which are not a part of the GROUP BY clause in an aggregrate function. Article Contributed by Sonal Tuteja. Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above. Which are Structured Query Language?Structured Query Language (SQL) is a standardized programming language that is used to manage relational databases and perform various operations on the data in them.
What is Structured Query Language with example?The most common example of SchemaQL is the 'CREATE TABLE' statement. Transact: Transact SQL provides the ability to insert, update, and delete data stored in a relational database. Data: The data query language is the part used to interact with stored data.
Is MySQL a Structured Query Language?SQL is developed by Microsoft Corporation. MySQL was developed by MySQL AB but is currently acquired and owned by Oracle Corporation. SQL is a structured query language used for managing and retrieving data from the database system. MySQL is a Relational database system that uses SQL to query data from the databases.
Is Oracle is a Structured Query Language?Structured Query Language (SQL)
The SQL implemented by Oracle Corporation for Oracle is 100% compliant at the Entry Level with the ANSI/ISO 1992 standard SQL data language. Oracle SQL includes many extensions to the ANSI/ISO standard SQL language, and Oracle tools and applications provide additional commands.
|