Test: SQL Server - 2 - Software Development MCQ

BOOLEAN is not a valid data type in SQL Server. The valid data types in SQL Server include VARCHAR, INT, FLOAT, and others, but BOOLEAN is not among them.

The SELECT statement is used to retrieve data from a database. It allows you to specify the columns to be selected and the table(s) from which to retrieve the data.

The UPDATE statement is used to modify existing data in a database. It allows you to update specific columns or rows in a table based on specified conditions.

The DELETE statement is used to delete data from a database. It allows you to delete specific rows from a table based on specified conditions.

The GROUP BY clause in SQL is used to group rows based on a specified column or columns. It is often used in conjunction with aggregate functions such as SUM, AVG, COUNT, etc.

The code creates a table named "Employees" with three columns: EmployeeID, FirstName, and LastName. It then inserts a single row into the table with values (1, 'John', 'Doe'). Finally, it selects all rows from the Employees table, which will return the inserted row: 1, 'John', 'Doe'.

The code creates a table named "Students" with three columns: StudentID, Name, and Age. It then inserts three rows into the table. The SELECT statement with COUNT(*) returns the total number of rows in the Students table, which is 3.

The code creates a table named "Orders" with four columns: OrderID, CustomerID, TotalAmount, and OrderDate. It then inserts three rows into the table. The SELECT statement with MAX(TotalAmount) returns the maximum value of the TotalAmount column, which is 200.75.

The code creates a table named "Employees" with three columns: EmployeeID, FirstName, and LastName. It then inserts three rows into the table. The SELECT statement with WHERE clause filters rows where LastName is 'Smith'. However, there is no row with LastName 'Smith' in the table, so it will result in an empty result set or an error depending on the database configuration.

The code creates a table named "Products" with four columns: ProductID, ProductName, UnitPrice, and UnitsInStock. It then inserts three rows into the table. The SELECT statement with SUM(UnitPrice * UnitsInStock) calculates the total value of all products by multiplying the UnitPrice and UnitsInStock columns for each row and summing them up. The result is 3499.00.

The code creates a table named "Employees" with four columns: EmployeeID, FirstName, LastName, and DepartmentID. It then inserts three rows into the table. The SELECT statement with GROUP BY DepartmentID counts the number of employees in each department. In this case, there are 2 employees in department 101 and 1 employee in department 102.

The code creates a table named "Students" with three columns: StudentID, Name, and Marks. It then inserts three rows into the table. The SELECT statement with AVG(Marks) calculates the average value of the Marks column, which is (90 + 80 + 95) / 3 = 88.33.

The code creates a table named "Orders" with four columns: OrderID, CustomerID, TotalAmount, and OrderDate. It then inserts three rows into the table. The SELECT statement with GROUP BY CustomerID calculates the sum of TotalAmount for each customer. In this case, the output will include the customer ID and the corresponding sum of TotalAmount for each customer.

The code creates a table named "Employees" with four columns: EmployeeID, FirstName, LastName, and Salary. It then inserts three rows into the table. The SELECT statement with WHERE clause filters rows where Salary is greater than 60000.00. In this case, it will return the row with 'Alice' as the FirstName and 70000.00 as the Salary.

The code creates a table named "Products" with four columns: ProductID, ProductName, UnitPrice, and UnitsInStock. It then inserts three rows into the table. The SELECT statement with ORDER BY UnitPrice DESC sorts the rows in descending order based on the UnitPrice column. Therefore, the output will be 'Monitor', 'Mouse', 'Keyboard' as the ProductName values in that order.