SQL
Write the correct SQL statements to create a new table called Persons
Create table Persons( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );
Write the correct SQL statement to delete a database named testDB
DROP DATABASE testDB
Delete all the records from the customers table
Delete from customers
Delete customers where county='norway'
Delete from customers where country ='norway'
Write the correct SQL statement to delete a table called Persons
Drop table Persons
Select all records from the Customers table, sort the result alphabetically by the column City.
SELECT * FROM Customers ORDER BY City
Use the between operator to select all the records where the value of the price column is not between 10 and 20
Select * from products where price not between 10 and 20
When displaying the customers table, make an alias of the postalcode column, the column should be pno instead
Select CustomerName, Address, PostalCode as Pno from Customers
The OR operator displays a record if ANY conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true
True
Use the TRUNCATE statement to delete all data inside a table
Truncate table persons
Try listing empnos from employee where the empno is less than 5 or greater than 20.
select empno from employee where empno<5 or empno>20
Try listing the empno and salary from jobhistory where the salary is greater than or equal to 20000.
select empno, salary from jobhistory where salary>=20000
With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" starts with an "a"?
select* from Persons where FirstName Like 'a%'
The NOT NULL constraint enforces a column to not accept NULL values.
true
Which SQL statement is used to update data in a database?
update
How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?
update Persons set LastName='Nilsen' where LastName='Hansen'
Add a column of type date called Birthday
Alter table person add Birthday date
Delete the column birthday from the persons table
Alter table persons drop column birthday
Write the correct SQL statement to create a new database called testDB
Create database testDB
With SQL, how can you return the number of records in the "Persons" table?
select count(*) from Persons
Which SQL statement is used to delete data from a database?
delete
With SQL, how can you delete the records where the "FirstName" is "Peter" in the Persons Table?
delete from Persons where firstname='Peter'
Which SQL statement is used to create a database table called 'Customers'?
Create table customers
Adapt this code to list empno and salary for positions that have an ENDDATE which is not null, and where the salary is greater or equal to 20000.
SELECT empno,salary FROM jobhistory WHERE salary >= 20000 AND enddate IS NOT NULL;
Update the City column of to equal Olso in the Customers table.
Update customer set city ='Oslo';
Update the city value to olso and the country value to norway where the customer id is 32
Update customers set city ='oslo', country='norway' where customerid=32;
Set the value of the City columns to 'Oslo', but only the ones where the Country column has the value "Norway"
Update customers set city='oslo' where country ='norway'
which operator is used to select values within a range?
between
what is the most common type of join?
inner
Which SQL statement is used to insert new data in a database?
insert into
With SQL, how can you insert "Olsen" as the "LastName" in the "Persons" table?
insert into Persons (LastName) values ('Persons')
Which SQL keyword is used to sort the result-set?
order by
With SQL, how can you insert a new record into the "Persons" table?
insert into Persons values ('Jimmy' ,'Jackson')
Which operator is used to search for a specified pattern in a column?
like
Which SQL statement is used to extract data from a database?
select
With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?
select * from Person where LastName Between 'Hansen' and 'Petterson'
With SQL, how do you select all the columns from a table named "Persons"?
select * from Persons
With SQL, how do you select all the records from a table named "Persons" where the "FirstName" is "Peter" and the "LastName" is "Jackson"?
select * from Persons where FirstName='Peter' And Lastname='Jackson'
With SQL, how can you return all the records from a table named "Persons" sorted descending by "FirstName"?
select * from persons order by firstname DESC
With SQL, how do you select a column named "FirstName" from a table named "Persons"?
select FirstName from Persons
Now find the course names (cname) in table COURSE where the cdate is between the start and end of 1988. A date looks like '01-Jan-1988', and you must always include the day, month, and year.
select cname from course where cdate between'01-Jan-1988' and '31-Dec-1988'
Which SQL statement is used to return only different values?
select distinct
When displaying the customers table, refer to the table Consumers instead of Customers
Select * from Customers as Consumers
With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" is "Peter"?
Select * from Persons where firstname='Peter'
Select all records from the Customers where the PostalCode column is empty.
Select * from customer where PostalCode IS NULL
Use the in operator to select all the records where country is either "Norway" or "France"
Select * from customer where country in ('Norway', 'France')
Select all records from the Customers where the PostalCode column is NOT empty.
Select * from customer where postalcode is not null
Select all records from the Customers table, sort the result reversed alphabetically by the column City
Select * from customers order by City desc
Select all records from the Customers table, sort the result alphabetically, first by the column Country, then, by the column City
Select * from customers order by country, city
Select all records where the value of the City column contains the letter "a"
Select * from customers where city like '%a%'
Select all records where the value of the City column ends with the letter "a"
Select * from customers where city like '%a'
Select all records where the first letter of the city is not an "a" or a "c" or an "f"
Select * from customers where city like '[!acf]%
Select all records where the first letter of the City starts with anything from an "a" to an "f"
Select * from customers where city like '[a-f]%'
Select all records where the first letter of the City is an "a" or a "c" or an "s"
Select * from customers where city like '[acs]%'
Select all records where the second letter of the city is an "a"
Select * from customers where city like '_a%'
Select all records where the value of the City column starts with the letter "a"
Select * from customers where city like 'a%'
Select all records where the value of the City column starts with letter "a" and ends with the letter "b"
Select * from customers where city like 'a%b'
Use the in operator to select all the records where Country is not "Norway" and not "France"
Select * from customers where country not in ('norway', 'france')
Use the NOT keyword to select all records where City is NOT "Berlin".
Select * from customers where not city ='Berlin'
Use the between operator to select all the records where the value of the ProductName column is alphabetically between 'Geitost' and 'Pavlova'
Select * from products where ProductName between 'Geitost' and 'Pavlova'
Use the between operator to select all the records where the value of Price column in between 10 and 20
Select * from products where price between 10 and 20
List the number of customers in each country
Select count(customerid), country from customers group by country
List the numbers of customers in each country, ordered by the country with the most customers first
Select count(customerid), country from customers group by country order by count(customerid) desc