(Copy table) Suppose the database contains a student table defined as follows: c
ID: 3831809 • Letter: #
Question
(Copy table) Suppose the database contains a student table defined as follows:
create table Student1 (
username varchar(50) not null,
password varchar(50) not null,
fullname varchar(200) not null,
constraint pkStudent primary key (username)
);
Create a new table named Student2 as follows:
create table Student2 (
username varchar(50) not null,
password varchar(50) not null,
firstname varchar(100),
lastname varchar(100),
constraint pkStudent primary key (username)
);
A full name is in the form of firstname mi lastname or firstname lastname. For example, John K Smith is a full name. Write a program that copies table Student1 into Student2. Your task is to split a full name into firstname, mi, and lastname for each record in Student1 and store a new record into Student2.
Explanation / Answer
Insert into student2 (username,password,firstname,lastname,pkStudent)
Select username, password, SUBSTR(fullname, 1, CHARINDEX(' ', fullname) - 1) AS firstname, REVERSE(SUBSTR(REVERSE(fullname), 1, CHARINDEX(' ', REVERSE(fullname)) - 1)) AS lastname,pkStudent
from Student;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.