Flashcards
Database Exam Revision
Cards in this deck (15)
- 1In SQL, how do you insert data?INSERT INTO example_table VALUES ('1234', 'Smith', 'John')
- 2What format would you write the date in SQL?01-JAN-2015
- 3Create a table called customer_list with custno(6), surname, firstname, acc_date. And set the properties assigned to them.CREATE TABLE customer_list
(
custno NUMBER(6), surname VARCHAR(20), firstname VARCHAR(20), acc_date DATE
) - 4Using the same customer_list table, add a primary key to it. (e.g. for primary key - assume another table uses acc_num)CREATE TABLE customer_list
(
custno NUMBER(6), surname VARCHAR(20), firstname VARCHAR(20), acc_date DATE, CONSTRAINT cust_pk (acc_num)
) - 5Using the same customer_list table (with the primary key included), add a foreign key to it (call it cust_fk1) and refrence this to the account_db. (e.g. for foreign key, use acc_num and assume acc_num is also included in account_db)CREATE TABLE customer_list
(
custno NUMBER(6), surname VARCHAR(20), firstname VARCHAR(20), acc_date DATE, CONSTRAINT cust_pk PRIMARY KEY (acc_num), CONSTRAINT cust_fk1 FOREIGN KEY(acc_num) REFRENCES account_db (acc_num)
) - 6Why is refrence required in SQL for foreign keys?Refrence allows the foreign key to be connected to another table that it i ascociated with (e.g. a primary key)
- 7Write a select statement that selects the first and second name that has an account set up before 1st January 2010 and accounts with no dates. (e.g. custno, surname, firstname, acc_date. Tablename is customer_list)SELECT firstname, surname, acc_date FROM customer_list WHERE acc_date < '01-JAN-2010' or acc_date is NULL
- 8Using the last select statement exercise, add an order by command so that the last name is in alphabetical order.SELECT firstname, surname, acc_date FROM customer_list WHERE acc_date < '01-JAN-2010' or acc_date is NULL ORDER BY surname ASC
- 9How would you change the time format in SQL to 1/jan/15?...(TO DATE('01-JAN-2015', 'dd/mmm/yy'))
- 10When are inner joins commonly used?Used when there are two tables that have the same column, they can then be joined together in a statement.
(e.g. order.accno = customer_acc.accno) - 11Create a subquery that shows which customer has the highest account balanceSELECT name FROM customer_table WHERE account = (SELECT MAX(sal) FROM customer_table);
- 12How do you update data?UPDATE example_table SET firstname='John', surname='smith' WHERE account_num ='1558';
- 13Write a delete statement that only deletes John Smiths accountDELETE FROM example_table WHERE firstname = 'John' AND surname ='Smith'
- 14Describe and give an example to what NVL is?NVL is a null value that prevents a value that when it adds a NULL, giving the answer NULL
e.g. (no NVL: 800 + 0 = NULL, with NVL: 800 + 0 = 800); - 15What is the basic structure of PLSQL?Variables [declare your variables]
begin [code must be here]
end;
print [output your results]
Information
- Description
- Flashcards on Database Exam Revision, created by b3014292 on 18/12/2014.
- Created by
- b3014292
- Views
- 56
- Rating