Grouping Rows With Same Values In Specified Columns
Thursday, June 13, 2024 in SQL Server Interview Questions
Grouping Rows With Same Values In Specified Columns SELECT col1_name, SUM(col2_name) FROM table_name GROUP BY col1_name; -- `GROUP BY` is used with aggregate functions to perform calculations on the grouped data -- every column in the SELECT clause …
Aggregate Function
Thursday, June 13, 2024 in SQL Server Interview Questions
Aggregate Function Perform a calculation on a set of values and return a single value SELECT AVG(col_name) FROM table_name -- we can use `AVG`, `MIN`, `MAX`, `SUM`, `COUNT` COUNT(*) Counts the total number of rows in the result set, even if some of …
Sorting Results
Thursday, June 13, 2024 in SQL Server Interview Questions
Sorting Results SELECT * FROM table_name ORDER BY col_name ASC; -- can use `ASC`, `DESC` SELECT column1, column2, column3 FROM table_name ORDER BY column1, column2 DESC, column3 ASC; -- ascending is the default sort order -- can `ORDER BY` multiple …
Filtering Rows Based On Conditions
Thursday, June 13, 2024 in SQL Server Interview Questions
Filtering Rows Based On Conditions SELECT col_name FROM table_name WHERE condition; -- condition can use `AND`, `OR`, `NOT`, `()` SELECT col_name FROM table_name WHERE col_name IN (val_list); -- or sub_query -- or `NOT IN` SELECT col_name FROM …
Query Without Duplicate Rows
Thursday, June 13, 2024 in SQL Server Interview Questions
Query Without Duplicate Rows SELECT DISTINCT col_name FROM table_name; SELECT DISTINCT column1, column2, column3 FROM table_name; SELECT COUNT(DISTINCT col_name) FROM table_name; DISTINCT Only get the unique values Can be used with multiple columns …
SELECT Execution Order
Thursday, June 13, 2024 in SQL Server Interview Questions
SELECT Execution Order 6. SELECT col, aggregate_func(col), windwo_func(col) OVER (PARTITION BY col ORDER BY col) 1. FROM table1 2. JOIN table2 ON table1.col1 = table2.col2 3. WHERE condition 4. GROUP BY col 5. HAVING condition 7. ORDER BY col --- 1. …
Delete Data In Table
Thursday, June 13, 2024 in SQL Server Interview Questions
Delete Data In Table -- without a join DELETE FROM comments WHERE commentID = 2; -- with a join DELETE comments FROM comments INNER JOIN posts ON comments.postID = posts.postID WHERE posts.title = 'First Post'; 196. Delete Duplicate Emails /* …
Update Data In Table
Thursday, June 13, 2024 in SQL Server Interview Questions
Update Data In Table UPDATE posts SET title = 'Updated First Post' WHERE postID = 1; 627. Swap Salary /* Write your T-SQL query statement below */ UPDATE Salary SET sex = CASE sex WHEN 'm' THEN 'f' ELSE 'm' END;
Create Database And Tables And Insert Data
Thursday, June 13, 2024 in SQL Server Interview Questions
Create Database And Tables And Insert Data -- Create Database CREATE DATABASE MyDb; GO -- Specify Database USE MyDb; GO -- Create Table CREATE TABLE Departments ( ID INT IDENTITY(1,1) PRIMARY KEY, FullName VARCHAR(255) NOT NULL ); GO -- Create Table …
Isolation Level
Thursday, June 13, 2024 in SQL Server Interview Questions
Isolation Level Isolation level refers to the degree to which a transaction is isolated from modifications made by other transactions eg. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; Concurrency problem in SQL database Dirty Reads Reading …