Scribd
Upload
9 views4 pages

SQL Operations on Student Table

The document outlines the structure of the Student and Subject tables, including their columns and sample data. It provides SQL commands for inserting data into the Student table, retrieving odd-numbered rows, and updating records. Additionally, it explains the syntax for updating multiple fields and using SELECT statements in UPDATE queries.

Uploaded by

d7360893
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
9 views4 pages

SQL Operations on Student Table

The document outlines the structure of the Student and Subject tables, including their columns and sample data. It provides SQL commands for inserting data into the Student table, retrieving odd-numbered rows, and updating records. Additionally, it explains the syntax for updating multiple fields and using SELECT statements in UPDATE queries.

Uploaded by

d7360893
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

The Student table consists of Student_ID, Stu_Name, Stu_Subject_ID, Stu_Marks, and Stu_Age

columns, and the Subject table consists of Subject_ID and Subject_Name columns.

Student Table:

Student_ID Stu_Name Stu_Subject_ID Stu_Marks Stu_Age


101 Akhil BCA101 85 20
102 Balram BCA104 78 19
103 Bheem BCA102 80 22
104 Chetan BCA103 95 20
105 Diksha BCA104 99 20
106 Raman BCA105 88 19
107 Sheetal BCA103 98 22
Column
Subject_ID Subject_Name
BCA101 C
BCA102 C++
BCA103 Principle of Management
BCA104 Core Java
BCA105 Math
BCA106 Android

query to insert the data into the table.


Sol:

Syntax to insert data into a table:

INSERT INTO Table_Name VALUES (value_1, value_2, value_3, ...., value_N);


We can easily insert the record using the INSERT statement in SQL.
following queries insert the data of students into Student table:

INSERT INTO Student VALUES (101, Akhil, BCA101, 85, 20);


INSERT INTO Student VALUES (102, Balram, BCA104, 78, 19);
INSERT INTO Student VALUES (103, Bheem, BCA102, 80, 22);
INSERT INTO Student VALUES (104, Chetan, BCA103, 95, 20);
INSERT INTO Student VALUES (105, Diksha, BCA104, 99, 20);
INSERT INTO Student VALUES (106, Raman, BCA105, 88, 19);
INSERT INTO Student VALUES (107, Sheetal, BCA103, 98, 22);

query in SQL to retrieve only an odd number of rows from the table?
Sol:

Syntax to find the Odd number of rows from the table:

SELECT * FROM Table_Name WHERE MOD (Rowid,2) = 1 ;


We can easily retrieve the odd rows from the table by using the MOD function in the WHERE
clause of the SELECT statement.
Example:

The following query shows odd rows of Student table in the result:

SELECT * FROM Student WHERE MOD (Rowid,2) = 1 ;


Output:

Student_ID Stu_Name Stu_Subject_ID Stu_Marks Stu_Age


101 Akhil BCA101 85 20
103 Bheem BCA102 80 22
105 Diksha BCA104 99 20
107 Sheetal BCA103 98 22

SQL UPDATE
The SQL commands (UPDATE and DELETE) are used to modify the data that is already in the
database. The SQL DELETE command uses a WHERE clause.

SQL UPDATE statement is used to change the data of the records held by tables. Which rows is
to be update, it is decided by a condition. To specify condition, we use WHERE clause.

The UPDATE statement can be written in following form:

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


condition]
Let's see the Syntax:

UPDATE table_name
SET column_name = expression
WHERE conditions

SQL statement:

UPDATE students
SET User_Name = 'beinghuman'
WHERE Student_Id = '3'
Source Table:

Student_Id FirstName LastName User_Name


1 Ada Sharma sharmili
2 Rahul Maurya sofamous
3 James Walker jonny

See the result after updating value:

Student_Id FirstName LastName User_Name


1 Ada Sharma sharmili
2 Rahul Maurya sofamous
3 James Walker beinghuman
Updating Multiple Fields:
If you are going to update multiple fields, you should separate each field assignment with a
comma.

SQL UPDATE statement for multiple fields:

UPDATE students
SET User_Name = 'beserious', First_Name = 'Johnny'
WHERE Student_Id = '3'
Result of the table is given below:

Result of the table is given below:

Student_Id FirstName LastName User_Name


1 Ada Sharma sharmili
2 Rahul Maurya sofamous
3 Johnny Walker beserious
MYSQL SYNTAX FOR UPDATING TABLE:

UPDATE table_name
SET field1 = new-value1, field2 = new-value2,
[WHERE CLAUSE]

SQL UPDATE SELECT:

SQL UPDATE WITH SELECT QUERY:

We can use SELECT statement to update records through UPDATE statement.

SYNTAX:

UPDATE tableDestination
SET [Link] = value
WHERE EXISTS (
SELECT [Link]
FROM tblSource
WHERE tblSource.join_col = tblDestination. Join_col
AND [Link] = value)
You can also try this one -

UPDATE
Table
SET
Table.column1 = [Link] 1,
Table.column2 = [Link] 2
FROM
Table
INNER JOIN
Other_table
ON
[Link] = other_table.id

Common questions

Powered by AI

An SQL DELETE command is more appropriate than an UPDATE command when you need to completely remove rows from a table rather than just modify certain values within them. DELETE is used when the data in some rows is no longer needed or if those rows are redundant, whereas UPDATE modifies existing data under certain conditions without removing the row itself .

The SQL JOIN operation is used to combine rows from two or more tables based on a related column between them. It allows data from different tables to be queried together as if they were one table. This is powerful because it enables comprehensive data analysis and retrieval from relational databases where data is often split across multiple tables to reduce redundancy and improve organization .

To insert a new record into a SQL table, you use the INSERT INTO statement followed by the table name and a specification of the values to be inserted. The basic syntax is 'INSERT INTO Table_Name VALUES (value_1, value_2, ... , value_N)'. Each part corresponds to the table and the specific data you want to insert, ensuring the data types and values match the table's columns .

The retrieval of odd-numbered rows from a database table can be achieved using the MOD function in SQL. You apply MOD to the Rowid and filter where the result is 1, indicating that the row is odd-numbered. The syntax is 'SELECT * FROM Table_Name WHERE MOD(Rowid, 2) = 1'. The logic is that Rowid is a sequential integer assigned by the database, and applying MOD with 2 highlights the odd (remainder 1) and even (remainder 0) distinctions .

Using an SQL SELECT statement within an UPDATE command involves the use of a subquery to specify conditions or set target values. The syntax commonly used is: 'UPDATE tableDestination SET tableDestination.col = value WHERE EXISTS (SELECT col2 FROM tblSource WHERE tblSource.join_col = tableDestination.join_col AND tblSource.Constraint = value)'. This is useful when you need to update records in one table based on criteria or data derived from another table, ensuring accuracy and relevance in data updates .

Altering the SQL syntax can significantly affect the outcome of queries. For instance, in an INSERT command, omitting a value for each column or inserting mismatched data types can result in syntax errors or data inconsistency. Similarly, in an UPDATE statement, using an incorrect WHERE clause can change unintended rows, while missing WHERE can update the whole table. Precise syntax ensures that data operations are executed accurately and efficiently .

The WHERE clause in SQL statements like DELETE and UPDATE specifies which rows in the table should be targeted by the command. Its absence can lead to altering or removing every record in the table, which might result in considerable data loss or unintended data modifications. Therefore, careful usage of the WHERE clause is essential to ensure changes are only made to intended records, reducing the risk of errors .

Using SQL functions like MOD involves considering data types and function limitations. The MOD function provides the remainder of division, critical in operations like identifying odd/even rows. Key considerations include ensuring that values used in MOD are integer-like (e.g., Rowid), as non-integer values can yield incorrect results. Proper understanding ensures better data manipulation and reliable query outcomes .

Updating multiple columns in an SQL statement allows for comprehensive data modification without needing multiple queries, thus maintaining efficiency in database management. For instance, the command 'UPDATE students SET User_Name = 'beserious', First_Name = 'Johnny' WHERE Student_Id = '3'' changes both the username and first name for a specific student. This approach minimizes query execution times and simplifies code management, especially in large databases .

In SQL, you can update multiple fields in a table using the UPDATE statement by separating each field assignment with a comma. For example, the syntax is 'UPDATE students SET columnName1 = value1, columnName2 = value2 WHERE condition'. The WHERE clause is critical when updating records because it specifies which rows should be updated; failing to use it appropriately could result in updating every row in the table .

You might also like