SQL

Question 1

Create an open database connection

Answer 1

$connection = new mysqli(“localhost”, “root”, “”, “”);

Question 2

What is used to terminate an SQL statement?

Answer 2

;

Question 3

Write SQL code to check for a connection error

Answer 3

if(mysqli_connect_errno()) {
echo “error message”
exit();
}

Question 4

Give examples of SQL data types

Answer 4

SMALLINT
VARCHAR
FLOAT
DATE

Question 5

Write SQL code to create a new database called school_info

Answer 5

$sql = "CREATE DATABASE school_info";
$result = $connection->query($sql)

Question 6

What is Database Management Language?

Answer 6

Data manipulation language refers to a set of commands used for selecting, inserting, deleting and updating data

Question 7

Create a new table in SQL that is called pupils and contains a student id, name, class and has student id as a primary key

Answer 7

$sql = "CREATE TABLE pupils(studentid INT(1) NOT NULL, name VARCHAR(40), class VARCHAR(10) PRIMARY KEY(studentid))";
$result = $connection->query($sql)

Question 8

What is the SELECT command used for?

Answer 8

It is used to pull data from the database to be displayed or to be processed in some way outwith the database.

Question 9

Write SQL code to close a database connection

Answer 9

mysqli_close($connection)

Question 10

What does SQL stand for?

Answer 10

Structured Query language

Question 11

Write SQL code to perform an implicit inner join on two tables student_grades and students, displaying all results where the student_grades id is the same the students id

Answer 11

SELECT * FROM student_grades, students WHERE student_grades.Id = students.Id;

Question 12

What is a Database Management System?

Answer 12

A collection of programs which enables its users to access the database and manipulate data

Question 13

Write generic select SQL code

Answer 13

SELECT field
FROM table
WHERE search criteria
ORDER BY sort criteria;

Question 14

Write generic SQL code to create a new database

Answer 14

$sql = "CREATE DATABASE dbname";
$result = $connection ->query($sql);

Question 15

Write SQL code to close a database connection

Answer 15

mysqli_close($connection);

Question 16

Write generic delete SQL code

Answer 16

DELETE FROM table

WHERE deletion criteria;

Question 17

Write generic update SQL code

Answer 17

UPDATE table
SET attributes to be updated
WHERE update criteria;

Question 18

Write generic insert SQL code

Answer 18

INSERT INTO table(attribute1, attribute2) VALUES(value1, value2):

Question 19

Write generic equi-join SQL code

Answer 19

SELECT table1.attribute, table2.attribute
FROM table1, table2
WHERE table1.attribute = table2.attribute;

Question 20

Write generic database connection

Answer 20

$connection = new mysqli(“localhost”,”username”,”password”, “database”);