Association Table - Create UserRoles Association Table for Users and Roles Table

An association (mapping) table, also called a link table or join table, is a type of table in a relational database that is used to associate(map) records from two or more other tables. Typically used in many-to-many relationships. In this case, any record in one table can be related to multiple records in another table and vice versa.

 An association (mapping) table typically contains a foreign key that references the primary key of the table to which it is mapped. 

Example of an association (mapping) table -

"usersRoles" table have two columns: userId and roleId. This table is used to associate "users" table with "roles" table in a many-to-many relationship. The primary key is a composite key made up of both columns (PRIMARY KEY(userId, roleId)), and there are foreign key constraints to ensure that the "userId" column references the "id" column in the "users" table and the "roleId" column references the "id" column in the roles table.

Depends on the role assigned to user user will be able access or perform the actions.

Please refer below SQL Statement,

 CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL
);

CREATE TABLE roles (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT
);

CREATE TABLE usersRoles (
    userId INTEGER NOT NULL,
    roleId INTEGER NOT NULL,
    PRIMARY KEY(userId, roleId),
    FOREIGN KEY(userId) REFERENCES users(id),
    FOREIGN KEY(roleId) REFERENCES roles(id)
);


-- Inserting data into the users table
INSERT INTO users (name, email, password)
VALUES ('John Doe', 'johndoe@example.com', 'password123'),
       ('Jane Smith', 'janesmith@example.com', 'mypassword'),
       ('Bob Johnson', 'bobjohnson@example.com', '123456');

-- Inserting data into the roles table
INSERT INTO roles (name, description)
VALUES ('Admin', 'Has full access to the system.'),
       ('Editor', 'Can edit and create content.'),
       ('Viewer', 'Can view content but cannot make changes.');
      
-- Inserting data into the usersRoles table
INSERT INTO usersRoles (userId, roleId)
VALUES (1, 1), -- John Doe is an Admin
       (2, 2), -- Jane Smith is an Editor
       (3, 3), -- Bob Johnson is a Viewer
       (1, 2), -- John Doe is also an Editor
       (2, 3); -- Jane Smith is also a Viewer

Read More

SQL LIKE Operator

The LIKE operator in SQL is used with the WHERE clause to get a result set that matches the given string pattern.

There are two wildcards often used with the LIKE operator,

1. % (percent sign) - represents zero, one, or multiple characters
2.  _ (underscore sign) - represents one, single character 

LIKE Operator Syntax

SELECT columnName,...
FROM tableName
WHERE columnName LIKE 'pattern';

Example of SQL LIKE operator & Wildcards

1. SQL Statement selects all Company with CompanyName that have value "Microsoft"

SELECT * FROM Company
WHERE CompanyName LIKE 'Microsoft';

2. SQL Statement selects all Company with CompanyName that have value "M" at the first position

SELECT * FROM Company
WHERE CompanyName LIKE 'M%';

3. SQL Statement selects all Company with CompanyName that have value "soft" at the end

SELECT * FROM Company
WHERE CompanyName LIKE '%soft';

4. SQL Statement selects all Company with CompanyName that have value "web" at any position

SELECT * FROM Company
WHERE CompanyName LIKE '%web%';

5. SQL Statement selects all Company with CompanyName that have value "o" at second position

SELECT * FROM Company
WHERE CompanyName LIKE '_o%';

6. SQL Statement selects all Company with CompanyName that have value "g" at first position and value "e" as last position

SELECT * FROM Company
WHERE CompanyName LIKE 'g%e';

Read More

SQL Query to find nth Lowest Salary

In this post, I will explain a SQL Query to find nth Lowest salary. 

In below query subquery returns the count of a distinct salary lower than current employee salary. Then the result of subquery will be compared with @N and record with subquery result equal @N will be returned as result.

DECLARE @N AS int = 2

SELECT * FROM Employee_Master EM1
WHERE @N  = (SELECT COUNT(DISTINCT(EM2.Salary)) FROM Employee_Master EM2 WHERE EM2.Salary < EM1.SALARY)

Table Script-

CREATE TABLE Employee_Master(
EmployeeId int IDENTITY(1,1) ,
EmployeeName varchar(100) NULL,
Salary decimal(18, 2) NULL,
) 

Read More

SQL Query to find nth Highest salary

In this post, I will explain a SQL Query to find nth Highest salary. 

In below query subquery returns the count of a distinct salary greater than current employee salary. Then the result of subquery will be compared with @N and record with subquery result equal @N will be returned as result.

DECLARE @N AS int = 2

SELECT * FROM Employee_Master EM1
WHERE @N  = (SELECT COUNT(DISTINCT(EM2.Salary)) FROM Employee_Master EM2 WHERE EM2.Salary > EM1.SALARY)

Table Script-

CREATE TABLE Employee_Master(
EmployeeId int IDENTITY(1,1) ,
EmployeeName varchar(100) NULL,
Salary decimal(18, 2) NULL,
) 

Read More

Difference between a Database and Schema?

Schema- A schema is the set of metadata (data dictionary) used by the database, typically generated using DDL. A schema defines attributes of the database, such as tables, columns, and properties. A database schema is a description of the data in a database. simply you can consider a schema to be a user account and collection of all objects therein.

Database- A database is a thing that contains all the schemas you've created, and their data (and a bunch of predefined system users, tables, views, etc. that make the whole things work). The database is the widest container, it collects schemas, tablespaces (datafiles are parts of a tablespace) and one or more (RAC) instances.

Read More

SQL Profiler

In this post, I will explain about SQL Profiler.

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performances by executing too slowly.

Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

Read More

How to delete duplicate rows using CTE in SQL Server?

In this post, I will explain How to delete duplicate rows using CTE in SQL Server.

A CTE stands for Common Table Expression. A CTE is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. A CTE always returns a result set.

 In below example, I will explain the use of CTE to delete duplicate rows from a table. In an example, I have table tbl_Student with two columns Name & BirthDate. In the table, tbl_Student have some duplicate rows. To achieve this I used CTE with SQL function ROW_NUBER() over partition by Name & BirthDate.

Table tbl_Student SQL Script

CREATE TABLE tbl_Student(

            Name varchar(100) NULL,

            BirthDate date NULL

)

  

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Rakesh', CAST(N'1990-03-12' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Nilesh', CAST(N'1978-03-20' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Sachin', CAST(N'1996-07-12' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Harshal', CAST(N'1989-05-12' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Rajesh', CAST(N'1985-03-05' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Rakesh', CAST(N'1996-03-08' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Rakesh', CAST(N'1990-03-12' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Nilesh', CAST(N'1978-03-20' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Sachin', CAST(N'1996-07-12' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Harshal', CAST(N'1989-05-12' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Rajesh', CAST(N'1985-03-05' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Sachin', CAST(N'1996-07-12' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Sachin', CAST(N'1996-07-12' AS Date))

Image 1 shows a table with data.

With CTE AS  

( 

SELECT          Name, BirthDate, ROW_NUMBER() OVER (PARTITION BY Name, BirthDate ORDER BY Name, BirthDate) AS SeqNo

FROM tbl_Student

) 

DELETE FROM CTE WHERE SeqNo > 1 ;

Image 2 shows a result set CTE.

 Image 3 shows the table after deleting duplicate rows.

Read More

Difference between Clustered Index and Non-Clustered Index

In this post, I will explain the difference between Clustered Index and Non-Clustered Index.

Indexes are used to quickly locate data without having to search every row in a database table. An index is a copy of selected columns of data from a table that can be searched very efficiently that also includes a low-level disk block address or direct link to the complete row of data it was copied from. 

 A table can have two types of indexes:

1. Clustered Index
2. Non-Clustered Index 

 Difference between Clustered Index and Non-Clustered Index

Sr No	Clustered Index	Non-Clustered Index
1	There can be only one Clustered Indexes for a table	There can be more than one Non-Clustered Indexes for a table
2	Data retrieval is faster than Non-Clustered Indexes	Data insertion/update is faster than Clustered Index
3	The leaf nodes of a Clustered Index contain the data pages	The leaf node of a Non-Clustered Index does not consist of the data pages. Instead, the leaf nodes contain index rows
4	Do not need extra space to store logical structure	Use extra space to store logical structure
5	Sort the records and store them physically according to the order	Do not affect the physical order. Create a logical order for data rows and use pointers to physical data files

Read More