SQL

¡Supera tus tareas y exámenes ahora con Quizwiz!

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

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

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

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'

Use the in operator to select all the records where country is either "Norway" or "France"

Select * from customer where country in ('Norway', 'France')

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


Conjuntos de estudio relacionados

303 Hinkle PrepU Chapter 42: Management of Patients With Musculoskeletal Trauma

View Set

Cell Signaling AP Biology Midterm Review

View Set

IMC 353 Chapter 8: narrative persuasion

View Set

Ch 16 Postpartum Nursing Management

View Set

Physical Science Final (from exam 3)

View Set

Lesson 3 Perfusion Adaptive Quizzing

View Set