Based on the following table What is the output of the SQL query table Student

Consider a table STUDENT having the following schema:

Inhaltsverzeichnis Show

SELECT statements
Learning more about SQL

STUDENT (Student_id, Student_Name, Address, Marks)

Student_id is the primary column of STUDENT table. Let first create the table structure with CREATE Command in SQL:

CREATE TABLE STUDENT (STUDENT_ID NUMBER (4), STUDENT_NAME VARCHAR2 (20), ADDRESS VARCHAR2 (20), MARKS NUMBER (3), PRIMARY KEY (STUDENT_ID));

Now, insert values into the table using INSERT INTO Command in SQL:

INSERT INTO STUDENT VALUES (100, ‘PUJA’, ’NOIDA’, 10); INSERT INTO STUDENT VALUES (101, ‘SUDO’, ’PUNE’, 30); INSERT INTO STUDENT VALUES (102, ‘BHALU’, ’NASHIK’, 40); INSERT INTO STUDENT VALUES (103, ‘CHETENA’, ’NOIDA’, 20); INSERT INTO STUDENT VALUES (104, ‘MOMO’, ’NOIDA’, 40);

Now display the content of STUDENT table:

SELECT * FROM STUDENT;Student_id Student_Name Address Marks ------------------------------------------------ 100 PUJA NOIDA 10 101 SUDO PUNE 30 102 BHALU NASHIK 40 103 CHETENA NOIDA 20 104 MOMO NOIDA 40

Query-1: Print the marks and number of student having marks more than the average marks of student from NOIDA city. Explanation: To get the average marks of student from NOIDA city we use this query:

SELECT AVG(MARKS) FROM STUDENT WHERE ADDRESS =’NOIDA’

We use this above sub query using GROUP BY and HAVING clause :

SELECT MARKS, COUNT (DISTINCT STUDENT_ID) FROM STUDENT GROUP BY MARKS HAVING MARKS > (SELECT AVG(MARKS) FROM STUDENT WHERE ADDRESS = ’NOIDA’ );

In the above query we use GROUP BY MARKS means it cluster the rows with same Marks and we also use SELECT MARKS, COUNT(DISTINCT STUDENT_ID) which prints the Marks of each cluster and the count of rows of respective clusters i.e.,

MARKS COUNT 10 1 20 1 30 1 40 2

After that we use HAVING MARKS > (SELECT AVG(MARKS) FROM STUDENT WHERE ADDRESS =’NOIDA’), which is used to filter the result with condition that marks must be greater than the avg marks of student from Noida city i.e., more than

(10+20+40) / 3 = 23.3

Output:

MARKS COUNT (DISTINCT STUDENT_ID) 30 1 40 2

Query-2: Display the Names and Addresses of the students whose name’s second letter is U. Explanation: For matching the pattern of the STUDENT_NAME field we used LIKE string comparison operator with two reserved character % and _ . % replaces an arbitrary number of characters, and ‘_’ replaces a single arbitrary character. Here, we need to compare the second letter of STUDENT_NAME thus we use the pattern ‘_U%’.

SELECT Student_Name, Address FROM STUDENT WHERE STUDENT_NAME LIKE ‘_U%’

Output:

STUDENT_NAME ADDRESS PUJA NOIDA SUDO PUNE

Query-3: Print the details of the student obtaining highest marks (if there is more than one student getting highest marks then highest will be according to the alphabetical order of their names). Explanation: To get the highest marks from the MARKS field we use the MAX command i.e.,

SELECT MAX(MARKS) FROM STUDENT;

We use the above sub-query which returns ‘40’ and it will be used with WHERE command. To arrange according to alphabetical order of STUDENT_NAME field we used ORDER BY clause and for getting the top row, LIMIT 1 will be used. Combining all these:

SELECT * FROM STUDENT WHERE MARKS = (SELECT MAX (MARKS) FROM STUDENT) ORDER BY STUDENT_NAME LIMIT 1;

Output:

Student_id Student_Name Address Marks 102 BHALU NASHIK 40

Query-4: Change the name and address of the student with ID 103 to RITA and DELHI respectively. Explanation: To change the value of any attributes we will use UPDATE command with SET clause to specify their new values.

UPDATE STUDENT SET STUDENT_NAME = ’RITA’, ADDRESS=’DELHI’ WHERE STUDENT_ID=103 ;

Output:

1 row updated

To see the changes we will use,

SELECT * FROM STUDENT;

Output:

Student_id Student_Name Address Marks 100 PUJA NOIDA 10 101 SUDO PUNE 30 102 BHALU NASHIK 40 103 RITA DELHI 20 104 MOMO NOIDA 40

Query-5: DELETE the details from the STUDENT table those are getting lowest mark. Explanation: To find the lowest mark we will use,

SELECT MIN(MARKS) FROM STUDENT;

It will return ‘10’ as a lowest marks. To delete rows we will use DELETE command with WHERE command for specify the condition.

DELETE FROM STUDENT WHERE MARKS = (SELECT MIN(MARKS) FROM STUDENT);

Output:

1 row affected

To see the changes we will use,

SELECT * FROM STUDENT;

Output:

Student_id Student_Name Address Marks 101 SUDO PUNE 30 102 BHALU NASHIK 40 103 RITA DELHI 20 104 MOMO NOIDA 40

Overview
SELECT statements
Examples
Learning more about SQL

Structured Query Language (SQL) is a specialized language for updating, deleting, and requesting information from databases. SQL is an ANSI and ISO standard, and is the de facto standard database query language. A variety of established database products support SQL, including products from Oracle and Microsoft SQL Server. It is widely used in both industry and academia, often for enormous, complex databases.

In a distributed database system, a program often referred to as the database's "back end" runs constantly on a server, interpreting data files on the server as a standard relational database. Programs on client computers allow users to manipulate that data, using tables, columns, rows, and fields. To do this, client programs send SQL statements to the server. The server then processes these statements and returns result sets to the client program.

SELECT statements

An SQL SELECT statement retrieves records from a database table according to clauses (for example, FROM and WHERE) that specify criteria. The syntax is:

SELECT column1, column2 FROM table1, table2 WHERE column2='value';

In the above SQL statement:

The SELECT clause specifies one or more columns to be retrieved; to specify multiple columns, use a comma and a space between column names. To retrieve all columns, use the wild card * (an asterisk).
The FROM clause specifies one or more tables to be queried. Use a comma and space between table names when specifying multiple tables.
The WHERE clause selects only the rows in which the specified column contains the specified value. The value is enclosed in single quotes (for example, WHERE last_name='Vader').
The semicolon (;) is the statement terminator. Technically, if you're sending only one statement to the back end, you don't need the statement terminator; if you're sending more than one, you need it. It's best practice to include it.

Examples

Following are examples of SQL SELECT statements:

To select all columns from a table (Customers) for rows where the Last_Name column has Smith for its value, you would send this SELECT statement to the server back end: SELECT * FROM Customers WHERE Last_Name='Smith';
The server back end would reply with a result set similar to this:
+---------+-----------+------------+ | Cust_No | Last_Name | First_Name | +---------+-----------+------------+ | 1001 | Smith | John | | 2039 | Smith | David | | 2098 | Smith | Matthew | +---------+-----------+------------+ 3 rows in set (0.05 sec)
To return only the Cust_No and First_Name columns, based on the same criteria as above, use this statement: SELECT Cust_No, First_Name FROM Customers WHERE Last_Name='Smith';
The subsequent result set might look like:
+---------+------------+ | Cust_No | First_Name | +---------+------------+ | 1001 | John | | 2039 | David | | 2098 | Matthew | +---------+------------+ 3 rows in set (0.05 sec)

To make a WHERE clause find inexact matches, add the pattern-matching operator LIKE. The LIKE operator uses the % (percent symbol) wild card to match zero or more characters, and the underscore ( _) wild card to match exactly one character. For example:

To select the First_Name and Nickname columns from the Friends table for rows in which the Nickname column contains the string "brain", use this statement: SELECT First_Name, Nickname FROM Friends WHERE Nickname LIKE '%brain%';
The subsequent result set might look like:
+------------+------------+ | First_Name | Nickname | +------------+------------+ | Ben | Brainiac | | Glen | Peabrain | | Steven | Nobrainer | +------------+------------+ 3 rows in set (0.03 sec)
To query the same table, retrieving all columns for rows in which the First_Name column's value begins with any letter and ends with "en", use this statement: SELECT * FROM Friends WHERE First_Name LIKE '_en';
The result set might look like:

+------------+------------+-----------+ | First_Name | Last_Name | Nickname | +------------+------------+-----------+ | Ben | Smith | Brainiac | | Jen | Peters | Sweetpea | +------------+------------+-----------+ 2 rows in set (0.03 sec)
If you used the % wild card instead (for example, '%en') in the example above, the result set might look like: +------------+------------+-----------+ | First_Name | Last_Name | Nickname | +------------+------------+-----------+ | Ben | Smith | Brainiac | | Glen | Jones | Peabrain | | Jen | Peters | Sweetpea | | Steven | Griffin | Nobrainer | +------------+------------+-----------+ 4 rows in set (0.05 sec)