Most commonly used SQL Queries | Part-1

SQL Queries, Here today we see that daily uses SQL queries and related problems solutions.

1. Define NULL in SQL with an example.

In simple words, A field with a NULL value is a field with no value. If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

NULL in SQL, as well as programming in general, means literally “nothing”. In SQL, it is easier to understand as “the absence of any value”

Example: SELECT * FROM TableName WHERE ColumnName IS NULL ;

2. How to Filtering for NULL in queries?

The syntax for filtering for NULL (i.e. the absence of a value) in WHERE blocks are slightly different than filtering for specific values. 

For Example: 

SELECT * FROM TableName WHERE EmployeeId IS NULL ; 

SELECT * FROM TableName WHERE EmployeeId IS NOT NULL ;

The word NULL is used to describe a missing value in SQL. In a table, a NULL value is a value in a field that appears to be empty. A field with a NULL value is the same as one that has no value.

3. Define Nullable columns in tables with examples. SQL

When you create the tables it is possible to that declare a column as nullable or non-nullable.

Example:

 CREATE TABLE EmpTable (
MyCol1 INT NOT NULL, -- non-nullable 
MyCol2 INT NULL -- nullable
) ; 

By default, every column (except those in the primary key constraint) is nullable unless we explicitly set the NOT NULL constraint. 

4. How to update NULL fields in SQL?

Setting a field to NULL works exactly like with any other value:

UPDATE Employees

SET ManagerId = NULL

 WHERE Id = 5 

5. How to Insert rows with NULL fields in SQL?

For example, inserting an employee with no phone number and no manager into the Employees example table:

INSERT INTO Employees

 (Id, FName, LName, PhoneNumber, ManagerId, DepartmentId, Salary, HireDate) 

VALUES (6, ‘Harry’, ‘Sharma’, NULL, NULL, 3, 600, ‘2022-12-11’) ; 

6. How to create a table for Auto Shop Database in SQL.

Here we take the Departments, Id, Name
1 HR
2 Sales
3 tech
SQL statements to create the table:

CREATE TABLE Departments (
Id INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(25) NOT NULL,
PRIMARY KEY(Id)
);
INSERT INTO Departments
([Id], [Name])
VALUES
(1, 'HR'),
(2, 'Sales'),
(3, 'Tech')
;

Follow: rtsall.com