Sql and Databases

Question 1

Select Statement in sql.

Answer 1

SELECT * from Tablename;

Select collumname,collumname2 from table name;

Question 2

How to select distinct values

Answer 2

Select Distinct collumname from tablename;

Question 3

what goes around a string when being matched

Answer 3

single quotes.

‘example’

Question 4

How to specify Selection order?

Answer 4

Select * From Tablename orderby collumname asc/desc;

(can have multiple ordering if a comma seperates them)

Question 5

Insertion of sql 2 methods

Answer 5

• Insert into tablename values(value1,value2,…..); (note: must have all table values)
• Insert Into tablename (collum1,collum2,…) values(value1,value2,…);

Question 6

Updating a table

Answer 6

UPDATE tablename

SET column1=value1, collumn2=value2,….

where some-collumn=somevalue;

Question 7

Deleting

Answer 7

Delete from tablename where somecolumn=somevalue;

(deletes a row)

Question 8

What does the wildcard % mean

Answer 8

substtues for 0 or more characters.

Question 9

what does the wildcard _ mean

Answer 9

substitutes for 1 character

Question 10

what does the wildcard [] , and [^ } mean

Answer 10

sets range of character to match, or to not match (with ^)

Question 11

Inner join

Answer 11

Returns the rows mentioned from multiple tables wher ethe condition is met.

Select collumnnames from table1 innerjoin table2 on t1.name=t2.name;

Question 12

usuing a where instead of a innerjoin

Answer 12

Select collumnames from tablenames where table1.ID=table2.ID;

note all table names are listed

Question 13

what is a union and unionall?

Answer 13

Union combines 2 or more select statements (only gets distrink values)

Unionall- the same but shows non distink values.

Question 14

Select into

Answer 14

Copies data from one table into a new table.

Select collumnnames into newtable from table1;

(note if where 1=0 is used a new table of the same form is created with no data)

Question 15

INsert into

Answer 15

Copies data from one table into an exisitng table.

Insert into table2 (collumname) select columnnames from table1;

Question 16

Create table

Answer 16

Create table tablename

columnname datatype(size) consttraints ;

Constanits eg not null,unique,primary key, forign key, default, check

Question 17

The constaints when creating a table?

Answer 17

• Not Null
• Unique
• Primary key
• forieign key
• default
• auto increment
• check

Question 18

Alternative way to add a primary key (and composite key) when creating a table.

Answer 18

Primary key(columnname,column2name)

Question 19

what datatype is a string?

Answer 19

varchar

Question 20

how to create a forign key?

Answer 20

columnname datatype Forign key references tablename(columnname)

Question 21

Alter table

Answer 21

• Alter table tablename add/alter columnname datatype
• Alter table tablename drop column columnname

Can be used to alter a contraint with alter constaintname(column)

Question 22

2 stages of normailsation

Answer 22

1. All data values are atomic - no repeating groups
2. Contains no partial key dependancies
3. contains no non-key dependancies