Go to the w3schools website’s SQL browser IDE. This is where you can write and test your SQL code using their databases. Once you are happy with it, paste your code in a text file named Student.txt and save it in your task folder. ● Write the SQL code to create a table called Student. The table structure is summarised in the table below (Note that STU_NUM is the primary key): Attribute Name Data Type STU_NUM CHAR(6) STU_SNAME VARCHAR(15) STU_FNAME VARCHAR(15) STU_INITIAL CHAR(1) STU_STARTDATE DATE COURSE_CODE CHAR(3) PROJ_NUM INT(2) ● After you have created the table in question 1, write the SQL code to enter the first two rows of the table as below: STU_NU M STU_SNA ME STU_FNA ME STU_INIT IAL STU_STA RTDATE COURSE _CODE PROJ_N UM 01 Snow John E 05-Apr-14 201 6 02 Stark Arya C 12-Jul-17 305 11 ● Assuming all the data in the Student table has been entered as shown below, write the SQL code that will list all attributes for a COURSE_CODE of 305. STU_NUM STU_SNAME STU_FNAME STU_INITIAL STU_STARTDATE COURSE_CODE PROJ_NUM 01 Snow Jon E 05-Apr-14 201 6 02 Stark Arya C 12-Jul-17 305 11 03 Lannister Jamie C 05-Sep-12 101 2 04 Lannister Cercei J 05-Sep-12 101 2 05 Greyjoy Theon I 9-Dec-15 402 14 06 Tyrell Margaery Y 12-Jul-17 305 10 07 Baratheon Tommen R 13-Jun-19 201 5 ● Write the SQL code to change the course code to 304 for the person whose student number is 07. ● Write the SQL code to delete the row of the person named Jamie Lannister, who started on 5 September 2012, whose course code is 101 and project number is 2. Use logical operators to include all of the information given in this problem. ● Write the SQL code that will change the PROJ_NUM to 14 for all those students who started before 1 January 2016 and whose course code is at least 201. ● Write the SQL code that will delete all of the data inside a table, but not the table itself. ● Write the SQL code that will delete the Student table entirely
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
Go to the w3schools website’s SQL browser IDE. This is where you can
write and test your SQL code using their
with it, paste your code in a text file named Student.txt and save it in your
task folder.
● Write the SQL code to create a table called Student. The table structure is
summarised in the table below (Note that STU_NUM is the primary key):
Attribute Name Data Type
STU_NUM CHAR(6)
STU_SNAME VARCHAR(15)
STU_FNAME VARCHAR(15)
STU_INITIAL CHAR(1)
STU_STARTDATE DATE
COURSE_CODE CHAR(3)
PROJ_NUM INT(2)
● After you have created the table in question 1, write the SQL code to enter
the first two rows of the table as below:
STU_NU
M
STU_SNA
ME
STU_FNA
ME
STU_INIT
IAL
STU_STA
RTDATE
COURSE
_CODE
PROJ_N
UM
01 Snow John E 05-Apr-14 201 6
02 Stark Arya C 12-Jul-17 305 11
● Assuming all the data in the Student table has been entered as shown
below, write the SQL code that will list all attributes for a COURSE_CODE of
305. STU_NUM
STU_SNAME
STU_FNAME
STU_INITIAL
STU_STARTDATE
COURSE_CODE
PROJ_NUM
01 Snow Jon E 05-Apr-14 201 6
02 Stark Arya C 12-Jul-17 305 11
03 Lannister Jamie C 05-Sep-12 101 2
04 Lannister Cercei J 05-Sep-12 101 2
05 Greyjoy Theon I 9-Dec-15 402 14
06 Tyrell Margaery Y 12-Jul-17 305 10
07 Baratheon Tommen R 13-Jun-19 201 5
● Write the SQL code to change the course code to 304 for the person whose
student number is 07.
● Write the SQL code to delete the row of the person named Jamie Lannister,
who started on 5 September 2012, whose course code is 101 and project
number is 2. Use logical operators to include all of the information given in
this problem.
● Write the SQL code that will change the PROJ_NUM to 14 for all those
students who started before 1 January 2016 and whose course code is at
least 201.
● Write the SQL code that will delete all of the data inside a table, but not the
table itself.
● Write the SQL code that will delete the Student table entirely
Step by step
Solved in 7 steps with 7 images
