Solved

Extracting Data in SQL

Posted on 2011-02-23
4
353 Views
Last Modified: 2012-05-11
My SQL is loading without any errors, but I cannot use this:

Select * from classroom; 
Select * from department; 
Select * from course; 
Select * from instructor; 
Select * from section; 
Select * from teaches; 
Select * from student; 
Select * from takes; 
Select * from advisor; 
Select * from prereq; 
Select * from timeslot; 

What is going on?
drop database if exists University;
create database University;
use University;

create table department(
dept_name varchar(20),
building  varchar(15),
budget    numeric(12, 2),
check (budget > 0),
index(dept_name),
primary key(dept_name)
);

create table instructor(

id varchar(20),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8, 2),
foreign key(dept_name) references department(dept_name)
    on delete set null,
primary key(id),
index(id)
);



create table course(

course_id varchar(8),
title varchar(50),
dept_name varchar(20),
credits numeric(2,0),
check (credits > 0), 
foreign key(dept_name) references department(dept_name)
    on delete set null,
primary key(course_id)

);

create table classroom(

building varchar(15),
room_number varchar(7),
capacity numeric(4, 0),
primary key(building,room_number),
index(building), #new index
index(room_number) #new index
);


create table section(

course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
building varchar(15),
room_number varchar(7),
time_slot_id varchar(4),

foreign key(course_id) references course(course_id)
    on delete cascade,
foreign key(building) references classroom(building)
    on delete set null,
foreign key(room_number) references classroom(room_number)
    on delete set null,
primary key(course_id,sec_id,semester,year),
#index(building),
#index(room_number),
index(course_id), #new index # doees it have anything to do with the indices?
index(sec_id), #new index
index(semester), #new index
index(year) #new index
);

create table teaches(

id varchar(20),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),


foreign key(course_id) references section(course_id) #department(sec_id) -> section(sec_id)
    on delete cascade,
foreign key(sec_id) references section(sec_id) #department(sec_id) -> section(sec_id)
    on delete cascade,
foreign key(semester) references section(semester) #department(sec_id) -> section(sec_id)
    on delete cascade,
foreign key(year) references section(year)
    on delete cascade,
foreign key(id) references instructor(id)
    on delete cascade,
primary key(id,course_id,sec_id,semester,year)
);




create table student(

id varchar(5),
name varchar(20) not null,
dept_name varchar(20),
tot_cred numeric(3,0),

foreign key(dept_name) references department(dept_name)
    on delete set null,
#primary key(id),
index(id) #new index

);


create table advisor(

s_id varchar(5),
i_id varchar(5),

foreign key(i_id) references instructor(id)
    on delete set null,

foreign key(s_id) references student(id)
    on delete cascade,

primary key(s_id)
#index(s_id)
);




create table takes(

id varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
grade varchar(2),

    foreign key(course_id) references section(course_id)
        on delete cascade,

    foreign key(sec_id) references section(sec_id)
        on delete cascade,

    foreign key(semester) references section(semester)
        on delete cascade,

    foreign key(year) references section(year) #section(year) -> section(year)
        on delete cascade,

    foreign key(id) references student(id)
        on delete cascade,

    primary key(id,course_id,sec_id,semester,year)

);

create table prereq(

course_id varchar(8),
prereq_id varchar(8),


    foreign key(course_id) references course(course_id)
        on delete cascade,

    foreign key(prereq_id) references course(course_id),

    primary key(course_id,prereq_id)

);

create table timeslot(

time_slot_id varchar(5),
day2 varchar(1),
start_hr int,
start_min int,
end_hr int,
end_min int,

primary key(time_slot_id,day2,start_hr,start_min)	                                 	

        	
);

load data infile 'j:/department.txt' into table department fields terminated by ',';
load data infile 'j:/instructor.txt' into table instructor fields terminated by ',';
load data infile 'j:/course.txt' into table course fields terminated by ',';
load data infile 'j:/classroom.txt' into table classroom fields terminated by ',';
load data infile 'j:/section.txt' into table section fields terminated by ',';
load data infile 'j:/teaches.txt' into table teaches fields terminated by ',';
load data infile 'j:/student.txt' into table student fields terminated by ',';
load data infile 'j:/advisor.txt' into table advisor fields terminated by ',';
load data infile 'j:/takes.txt' into table takes fields terminated by ',';
load data infile 'j:/prereq.txt' into table prereq fields terminated by ',';
load data infile 'j:/timeslot.txt' into table timeslot fields terminated by ',';

Open in new window

University.zip
0
Comment
Question by:JCW2
  • 2
4 Comments
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 34967338
At the end of your variable declarations you have an extra comma. For example,
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
building varchar(15),
room_number varchar(7),
time_slot_id varchar(4),

should be:
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
building varchar(15),
room_number varchar(7),
time_slot_id varchar(4)

0
 
LVL 17

Expert Comment

by:Shinesh Premrajan
ID: 34967342
you need to grant the user privileges to the database, hope that is done?
0
 

Accepted Solution

by:
JCW2 earned 0 total points
ID: 34967358
Apparently the select statements couldn't be all in one row.

That is, I needed Select * from
                         classroom;    or
                         Select *
                         from classroom;
instead of Select * from classroom;
0
 

Author Closing Comment

by:JCW2
ID: 34995593
This answer got the data printed out for me.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Set Warning Thresholds on Database mirroring 4 34
Amazon Redshift 2 25
Need a starter for ETL protocol? 4 39
Exchange 2010 Database wont mount 3 23
Creating and Managing Databases with phpMyAdmin in cPanel.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now