- What are the basic data types supported in T-SQL?
- What is a primary key and how is it different from a foreign key?
- What is a join in T-SQL? Explain with an example.
- How do you create a new table in T-SQL?
- How do you insert data into a table in T-SQL?
- What is a stored procedure and how do you create one in T-SQL?
- What is a trigger in T-SQL and how do you create one?
- How do you update data in a table in T-SQL?
- What is a view in T-SQL and how do you create one?
- What is the difference between the DELETE and TRUNCATE commands in T-SQL?
- How do you select data from a table in T-SQL?
- What is a subquery in T-SQL? Explain with an example.
- What is the purpose of the GROUP BY clause in T-SQL? Explain with an example.
- What is a cursor in T-SQL and how do you use it?
- What is the difference between a CHAR and VARCHAR data type in T-SQL? How does this impact storage and performance?
- How do you create a composite primary key in T-SQL? Provide an example.
- What is the purpose of a clustered index in T-SQL? How do you create one?
- What is a subselect in T-SQL? Provide an example.
- How do you insert multiple rows into a table in T-SQL?
- What is the difference between a temporary table and a table variable in T-SQL? When would you use one over the other?
- How do you alter a table in T-SQL? Provide an example.
- What is the difference between a function and a stored procedure in T-SQL?
- What is the purpose of the ROLLBACK statement in T-SQL? When would you use it?
- What is the purpose of the TOP clause in T-SQL? Provide an example.
- You have a table called Customers that contains the columns CustomerID, FirstName, LastName, and Email. Write a T-SQL query to select all customers whose first name is "John".
- You have a table called Orders that contains the columns OrderID, OrderDate, CustomerID, and TotalAmount. Write a T-SQL query to select the order with the highest total amount.
- You have a table called Products that contains the columns ProductID, ProductName, CategoryID, and Price. Write a T-SQL query to select all products in the "Electronics" category.
- You need to create a new table called Sales that will contain the columns ProductID, OrderID, and Quantity. Write a T-SQL query to create this table.
- You have a table called Employees that contains the columns EmployeeID, FirstName, LastName, and HireDate. Write a T-SQL query to select all employees who were hired in the year 2021.
- You have a table called Students that contains the columns StudentID, FirstName, LastName, and Age. Write a T-SQL query to select all students whose age is greater than 18.
- You have a table called Customers that contains the columns CustomerID, FirstName, LastName, and Email. Write a T-SQL query to select all customers whose last name is "Smith".
- You have a table called Products that contains the columns ProductID, ProductName, CategoryID, and Price. Write a T-SQL query to select all products with a price greater than $50.
- You have a table called Orders that contains the columns OrderID, OrderDate, CustomerID, and TotalAmount. Write a T-SQL query to select all orders placed in the month of January.
- You have a table called Employees that contains the columns EmployeeID, FirstName, LastName, and HireDate. Write a T-SQL query to select all employees who were hired before January 1, 2021.
- What is normalization and how does it apply to database design in T-SQL?
- What is the purpose of the HAVING clause in T-SQL? Explain with an example.
- What is the difference between an inner join and an outer join in T-SQL? Provide an example of each.
- How do you create an index in T-SQL? Why is indexing important?
- What is a transaction in T-SQL? How do you ensure data consistency in a transaction?
- What is the purpose of the CASE statement in T-SQL? Provide an example.
- What is a scalar function in T-SQL? How do you create one?
- What is a table-valued function in T-SQL? How do you create one?
- What is a CTE (Common Table Expression) in T-SQL? Provide an example.
- What is dynamic SQL in T-SQL? How do you create and execute dynamic SQL statements?
- What is a covering index in T-SQL? How does it improve query performance?
- What is a filtered index in T-SQL? How does it improve query performance?
- What is the purpose of the EXISTS keyword in T-SQL? Provide an example.
- What is the difference between a LEFT and RIGHT join in T-SQL? Provide an example of each.
- What is a recursive CTE in T-SQL? Provide an example.
- What is a correlated subquery in T-SQL? Provide an example.
- What is the purpose of the WAITFOR statement in T-SQL? When would you use it?
- What is the difference between a function and a trigger in T-SQL?
- What is the difference between the UNION and UNION ALL operators in T-SQL? Provide an example of each.
- What is the purpose of the OVER clause in T-SQL? Provide an example.
- You have a table called Orders that contains the columns OrderID, OrderDate, CustomerID, and TotalAmount. Write a T-SQL query to select all orders that were placed in the year 2021 and have a total amount greater than $100.
- You have a table called Customers that contains the columns CustomerID, FirstName, LastName, and Email. Write a T-SQL query to select all customers who have placed an order.
- You have a table called Products that contains the columns ProductID, ProductName, CategoryID, and Price. Write a T-SQL query to select the top 10 products by price.
- You need to create a stored procedure called GetOrderDetails that will accept an OrderID parameter and return the order details (including customer name, order date, and total amount). Write a T-SQL script to create this stored procedure.
- You have a table called Employees that contains the columns EmployeeID, FirstName, LastName, and HireDate. Write a T-SQL query to select the top 5 employees with the most seniority.
- You have a table called Orders that contains the columns OrderID, OrderDate, CustomerID, and TotalAmount. Write a T-SQL query to select all orders with a total amount between $100 and $500.
- You have a table called Customers that contains the columns CustomerID, FirstName, LastName, and Email. Write a T-SQL query to select all customers who have not placed an order.
- You have a table called Products that contains the columns ProductID, ProductName, CategoryID, and Price. Write a T-SQL query to select all products in the "Clothing" category with a price greater than $25.
- You have a table called Employees that contains the columns EmployeeID, FirstName, LastName, and HireDate. Write a T-SQL query to select all employees who were hired in the year 2020 or later.
- You have a table called Orders that contains the columns OrderID, OrderDate, CustomerID, and TotalAmount. Write a T-SQL query to select all orders with a total amount greater than the average total amount of all orders.
- What is a temp table in T-SQL? How do you create one? What are the advantages and disadvantages of using temp tables?
- What is a partitioned table in T-SQL? How do you create one? What are the advantages and disadvantages of using partitioned tables?
- What is a MERGE statement in T-SQL? How do you use it?
- What is the purpose of the TRY...CATCH block in T-SQL? How do you use it?
- What is a query plan in T-SQL? How do you view and analyze it?
- What is a correlated subquery in T-SQL? Provide an example.
- What is a common table expression (CTE) recursive query in T-SQL? Provide an example.
- What is the difference between a clustered and non-clustered index in T-SQL? Provide an example of when to use each.
- What is a filtered index in T-SQL? How do you create one?
- What is a full-text index in T-SQL? How do you create and use one?
- What is a materialized view in T-SQL? How do you create and use one?
- What is a spatial index in T-SQL? How do you create and use one?
- What is a XML index in T-SQL? How do you create and use one?
- What is the purpose of the XML data type in T-SQL? Provide an example of how it can be used.
- What is a recursive trigger in T-SQL? How do you create one?
- What is the purpose of the FILESTREAM data type in T-SQL? How do you use it?
- What is the difference between a table-valued parameter and a temporary table in T-SQL? Provide an example of each.
- What is the difference between a HEAP and a clustered index in T-SQL? Provide an example of each.
- What is the purpose of the OUTPUT clause in T-SQL? Provide an example.
- What is a window function in T-SQL? Provide an example.
- What is the difference between a scalar function and a table-valued function in T-SQL? Provide an example of each.
- What is the difference between the TRY_CONVERT and CONVERT functions in T-SQL? Provide an example of each.
- What is a sparse column in T-SQL? How do you create one? What are the advantages and disadvantages of using sparse columns?
- What is the purpose of the ROW_NUMBER function in T-SQL? Provide an example.
- What is a temporal table in T-SQL? How do you create and use one?
- What is the difference between a physical and logical delete in T-SQL? Provide an example of each.
- What is the purpose of the PIVOT and UNPIVOT operators in T-SQL? Provide an example of each.
- You have a table called Products that contains the columns ProductID, ProductName, CategoryID, and Price. Write a T-SQL query to select the top 5 categories by total sales amount (based on the products in each category).
- You have a table called Orders that contains the columns OrderID, OrderDate, CustomerID, and TotalAmount. Write a T-SQL query to select the top 10 customers with the highest average order total.
- You need to create a trigger that will automatically update the TotalAmount column in the Orders table whenever a new order is inserted. Write a T-SQL script to create this trigger.
- You have a table called Sales that contains the columns ProductID, OrderID, and Quantity. Write a T-SQL query to select the total sales quantity for each product.
- You have a table called Customers that contains the columns CustomerID, FirstName, LastName, and Email. Write a T-SQL query to select the top 5 customers who have placed the most orders.
- You have a table called Orders that contains the columns OrderID, OrderDate, CustomerID, and TotalAmount. Write a T-SQL query to select the top 10 customers with the highest lifetime order total (i.e., the total amount of all orders placed by each customer).
- You have a table called Customers that contains the columns CustomerID, FirstName, LastName, and Email. Write a T-SQL query to select all customers who have placed an order in the last 30 days
- You have a table called Orders that contains the columns OrderID, OrderDate, CustomerID, and TotalAmount. Write a T-SQL query to select the top 5 customers with the highest number of orders in the last year.
- You have a table called Products that contains the columns ProductID, ProductName, CategoryID, and Price. Write a T-SQL query to select all products that have never been ordered.
- You have a table called Sales that contains the columns ProductID, OrderID, and Quantity. Write a T-SQL query to select all products that have been ordered more than 100 times.
- You have a table called Employees that contains the columns EmployeeID, FirstName, LastName, and HireDate. Write a T-SQL query to select all employees who have been employed for more than 5 years.
- You have a table called Orders that contains the columns OrderID, OrderDate, CustomerID, and TotalAmount. Write a T-SQL query to select the top 10 customers with the highest lifetime order total (i.e., the total amount of all orders placed by each customer).