Download SQL Query Exercises and more Exams Service Management in PDF only on Docsity!
D
Write a Query that Selects all fields from "Customers" where country is "Germany" AND city must be "Berlin" OR "Stuttgart" - correct answer ✔SELECT * FROM Customers WHERE Country = 'Germany' AND (City = 'Berlin' OR City = 'Stuttgart'); Write a Query that selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column. This means that it orders by Country, but if some rows have the same Country, it orders them by CustomerName - correct answer ✔SELECT * FROM Customers ORDER BY Country, CustomerName; Insert a new record into the "Customers" table, but only insert data in the "CustomerName", "City", and "Country" columns - correct answer ✔INSERT INTO Customers (CustomerName, City, Country) VALUES ('Rick', 'Lexington', 'USA'); Lists all customers from the Customers table with a NULL value in the "Address" field - correct answer ✔SELECT * FROM Customers WHERE Address IS NULL; List all customers from the Customers table without a NULL value in the CustomerName field - correct answer ✔SELECT * FROM Customers WHERE CustomerName IS NOT NULL;
Update the first customer (CustomerID = 1) with a new contact person and a new city in the Customer Table - correct answer ✔UPDATE Customer SET ContactPerson = 'John', City = 'Lexington' WHERE CustomerID = 1; Deletes the customer "Alfreds Futterkiste" from the "Customers" table - correct answer ✔DELETE FROM Customers WHERE CustomerName = 'Alfreds Futterkiste'; Delete all records from the Customers table - correct answer ✔DELETE FROM Customers; Write a query statement that finds the price of the cheapest product in the Products table - correct answer ✔SELECT MIN(Price) FROM Products; Write a query that finds the maximum population in the USA from the World Table - correct answer ✔SELECT MAX(Population) FROM World WHERE Country = 'USA'; Write a query to find the total number of Users in the Google table - correct answer ✔SELECT COUNT(UserID) FROM Google; Write a query to find the average amount of cars in the Neighborhood Table - correct answer ✔SELECT AVG(Cars)
FROM Customers WHERE CustomerName LIKE 'a_%'; Write a query that selects all customers with a ContactName that starts with "a" and ends with "o" - correct answer ✔SELECT * FROM Customers WHERE ContactName LIKE 'a%o'; Write a query that selects all customers with a CustomerName that does NOT start with "a" - correct answer ✔SELECT * FROM Customers WHERE CustomerName NOT LIKE 'a%'; Write a query that selects all customers that are located in "Germany", "France" or "UK" - correct answer ✔SELECT * FROM Customers WHERE Country IN ('Germany,', 'France', 'UK'); Write a query that selects all customers that are NOT located in "Germany", "France" or "UK" - correct answer ✔SELECT * FROM Customers WHERE Country NOT IN ('Germany,', 'France', 'UK'); Write a query that selects all customers that are from the same countries as the suppliers - correct answer ✔SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers);
selects all products with a price between 10 and 20 - correct answer ✔SELECT * FROM Products WHERE Price BETWEEN '10' AND '20'; SQL statement selects all products with a price between 10 and 20. In addition; do not show products with a CategoryID of 1,2, or 3 - correct answer ✔SELECT * FROM Products WHERE Price BETWEEN '10' AND '20' AND CategoryID NOT IN ('1','2','3'); SQL statement creates two aliases, one for the CustomerID column and one for the CustomerName column - correct answer ✔SELECT CustomerID AS ID, CustomerName AS 'Consumer Name' FROM Customers; Write an inner join statement that joins a Customers table ,Orders table with a common CustomerID column and shows the OrderID and CustomerName - correct answer ✔SELECT Customer.CustomerName, Orders.OrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID; Write an inner join statement that joins a Customers table ,Orders table and a Suppliers table with a common CustomerID column and shows the OrderID and CustomerName and SupplierName - correct answer ✔SELECT Customers.CustomerName, Orders.OrderID, Suppliers.SupplierName FROM ((Customers
WHERE A.CustomerName<>B.CustomerName AND A.City=B.City ORDER BY A.City; SQL statement returns the cities (only distinct values) from both the "Customers" and the "Suppliers" table - correct answer ✔SELECT Cities FROM Customers UNION SELECT Cities FROM Suppliers; SQL statement returns the German cities (only distinct values) from both the "Customers" and the "Suppliers" table - correct answer ✔SELECT Cities FROM Customers WHERE Country ='Germany' UNION SELECT Cities FROM Suppliers WHERE Country ='Germany'; SQL statement lists the number of customers in each country - correct answer ✔SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country; SQL statement lists the number of customers in each country, sorted high to low - correct answer ✔SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country ORDER BY COUNT(CustomerID) desc;
SQL statement lists the number of orders from the Order table sent by each shipper in the Shippers table - correct answer ✔SELECT Shippers.ShipperName AS Suppliers COUNT(Orders.OrderID) AS OrderNumber, FROM Shippers LEFT JOIN Orders ON Orders.ShipperID=Shippers.ShipperID GROUP BY ShipperName; SQL statement lists the number of customers in each country. Only include countries with more than 5 customers, ordered from low to high - correct answer ✔SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID)> ORDER BY COUNT(CustomerID); SQL statement returns TRUE and lists the suppliers with a product price less than 20 - correct answer ✔SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID=Suppliers.SupplierID AND Price<20); creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City: - correct answer ✔CREATE TABLE Persons ( PersonID INT
Age INT Name varchar(50) UNIQUE (ID) ); In the Persons table, create a UNIQUE constraint on the "ID" column when the table is already created - correct answer ✔ALTER TABLE Persons ADD UNIQUE(ID); SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is created - correct answer ✔CREATE TABLE Persons ( ID INT Age INT Name varchar(50) PRIMARY KEY (ID) ); To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use the following SQL - correct answer ✔ALTER TABLE Persons ADD PRIMARY KEY (ID); The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table is created - correct answer ✔CREATE TABLE Orders ( OrderID INT PersonID INT OrderName varchar(50) FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
SQL creates a CHECK constraint on the "Age" column when the "Persons" table is created. The CHECK constraint ensures that the age of a person must be 18, or older - correct answer ✔CREATE TABLE Persons ( PersonID INT Age INT Name varchar(50) CHECK (age>=18) ); create a CHECK constraint on the "Age" column when the Persons table is already created - correct answer ✔ALTER TABLE Persons ADD CHECK (Age>=18); SQL sets a DEFAULT value for the "City" column when the "Persons" table is created - correct answer ✔CREATE TABLE Persons ( PersonID INT Age INT City varchar(50) DEFAULT 'Unknown' ); create a DEFAULT constraint on the "City" column when the table is already created - correct answer ✔ALTER TABLE Persons Alter City SET DEFAULT 'Lexington'; creates an index named "idx_lastname" on the "LastName" column in the "Persons" table - correct answer ✔CREATE INDEX idx_name ON Persons (LastName);
