SQL Test 3

Home > Preview

The flashcards below were created by user david20x6 on FreezingBlue Flashcards.


  1. Order By
    • SELECT * FROM Book ORDER BY author;
    • SELECT * FROM Book ORDER BY author DESC;
    • SELECT * FROM Book ORDER BY pages LIMIT 3;
  2. INSERT
    INSERT INTO Book VALUES (1234, 'Help with SQL', 245, 'Smith');
  3. ALTER TABLE
    • ALTER TABLE Book ADD primary key (isbn);
    • ALTER TABLE Book DROP primary key;
    • ALTER TABLE Book ADD (publisher varchar(20));
    • ALTER TABLE Book DROP publisher;
  4. DELETE
    DELETE FROM Book WHERE title='Help with SQL';
  5. UPDATE
    UPDATE Book SET title='The New SQL' WHERE isbn = 5555;
  6. CREATE TABLE
    • CREATE TABLE CITY(
    • city_num int not null auto_increment,
    • city_name varchar(30),
    • state char(2),
    • population int,
    • founded date,
    • primary key (CITY_num));
  7. INSERT
    INSERT INTO CITY (city_name, state, population, founded) VALUES('Philidelphia','PA',2000000,'1690-02-01');
  8. ALIASES
    SELECT city_name AS "CITY" FROM CITY;
  9. DISTINCT - limits the result to one of each
    SELECT DISTINCT state FROM CITY;
  10. BETWEEN - used with dates and values or strings
    SELECT * FROM CITY WHERE founded BETWEEN '16900101' AND '17600101';
  11. ANDs and ORs - Use ( ) to maintain order
    SELECT * FROM CITY WHERE population BETWEEN 1 AND '99999' OR state = 'PA';
  12. NOT - rules out a characteristic
    SELECT * FROM CITY WHERE NOT(state='pa');
  13. LIKE - to find some part of the field
    SELECT * FROM CITY WHERE city_name LIKE 'Ne%';
  14. IN - place the list of matches in ( ) separated by commas
    SELECT * FROM CITY WHERE state IN ('pa','ny');
  15. MIN and MAX
    SELECT MAX(population) FROM CITY;
  16. COUNT
    SELECT COUNT(population) FROM CITY;
  17. SUM
    SELECT SUM(population) FROM CITY;
  18. AVERAGE
    SELECT AVG(population) FROM CITY;
  19. GROUP BY - will categorize your results
    • SELECT state, AVG(population)
    • FROM CITY
    • WHERE founded > 1700-01-01
    • GROUP BY state;
  20. HAVING
    • SELECT state, AVG(population)
    • FROM CITY
    • WHERE founded > 1700-01-01
    • GROUP BY state
    • HAVING COUNT(state) > 1;
  21. VIEWS
    CREATE VIEW PACities AS SELECT * FROM CITY WHERE state = 'PA';
  22. SUBSTRING
    • SELECT SUBSTRING(name FROM 1 for 3)
    • FROM US_STATES
  23. UPPER and LOWER case
    • SELECT upper(name), lower(abr)
    • FROM US_STATES;
  24. TIME and Date Stamps
    SELECT Current_date as "Date", Current_Time as "Time", Current_timestamp as "Timestamp";
  25. List all of the databases available
    SHOW DATABASES;
  26. show all of the tables available
    SHOW TABLES;
  27. show a tables fields and attributes
    DESCRIBE Books;
  28. remove a table and all of its data
    DROP TABLE Book;
  29. DELETE a record
    DELETE FROM Book WHERE title = 'Help with SQL';
  30. INNER JOIN
    • SELECT * FROM COUNTRY INNER JOIN STATE
    • on COUNTRY.countryid = STATE.countryid;
  31. LEFT JOIN
    SELECT * FROM COUNTRY LEFT JOIN STATEon COUNTRY.countryid = STATE.countryid;
  32. RIGHT JOIN
    SELECT * FROM COUNTRY RIGHT JOIN STATEon COUNTRY.countryid = STATE.countryid;

Card Set Information

Author:
david20x6
ID:
319537
Filename:
SQL Test 3
Updated:
2016-04-29 02:41:46
Tags:
SQL
Folders:

Description:
test 3
Show Answers:

Home > Flashcards > Print Preview