Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

need mysql search/Query help

i have this database(at bottom of post) i created and filled with info now i want to run some searches but i'm sort of lost on how to do some of these.

i want to Display a class roll it needs to show there SSN, lastName, firstName of those enrolled in Fall 2004 CIS3600 class and sort it by Lastname,Firstname

and i want to take what i'm doing from above,and Convert all the lastnames and firstnames to UPPER CASE

and i want to update all course descriptions to be non-uppercase. So 'UPPER CASE' should be 'Upper Case'.
i've done some here on this one but not sure if i'm on the right track or how to go from here
update Course set desr=concat(upper(left(Course.descr,1))) lower(right(Course.decr

i also want to list the instructor with the maximum number of classes teaching in Fall 2004.
this is what i've started but i'm not sure if i'm on the right track or how to go from here
select i>firstName,sum(Course.credits,as sumer From Teaches AS T,inner join Instructor as i on (i.instructorID) join Section AS se on (se, sectionID=T.sectionID) join

i need to find out how much each student that is enrolled in Fall of 2004 would have to pay if they owed say $120 per credit. i need to Show the amount, lastName, and firstName.
this is the little bit i have from starting on it

select Season.seasonName,Semester.year concat('$', format 120 *(sum(Course.credits)),3 )) AS sumer


simular to the previous one i think if i could figure out the previous one above i could figure these two

i want to find out how much each student has payed had they taken classes before Fall 2004 assuming the price from above. it should have a '$' and ','s in there.

i want to find out how much the school has gotten from students for each semester assuming the price from above(needs '$' and ','s in there).



My database
DROP DATABASE IF EXISTS courses;
CREATE DATABASE courses;
use courses;
DROP TABLE IF EXISTS Course;
CREATE TABLE Course
(
courseID INT(10) UNSIGNED NOT NULL auto_increment primary key,
courseNumber varchar(8) NOT NULL,
title VARCHAR(64),
descr TEXT DEFAULT NULL,
credits TINYINT UNSIGNED DEFAULT 0,
UNIQUE (courseNumber),
INDEX id_key (courseID)
)TYPE=InnoDB;

DROP TABLE IF EXISTS Season;
CREATE TABLE Season (
seasonID INT(10) UNSIGNED NOT NULL auto_increment PRIMARY KEY,
seasonName VARCHAR(24) DEFAULT NULL,
seasonAbbr CHAR(2) DEFAULT NULL,
INDEX seasonID_key (seasonID)
)TYPE=InnoDB;
DROP TABLE IF EXISTS Instructor;
CREATE TABLE Instructor (
instructorID INT(10) UNSIGNED NOT NULL auto_increment PRIMARY KEY,
lastName VARCHAR(24) DEFAULT NULL,
firstName VARCHAR(18) DEFAULT NULL,
email VARCHAR(64) DEFAULT NULL,
roomID VARCHAR(12) DEFAULT NULL,
phone VARCHAR(12) DEFAULT NULL,
INDEX instructorID_key (instructorID)
)TYPE=InnoDB;
DROP TABLE IF EXISTS Semester;
CREATE TABLE Semester
(
semesterID INT(10) UNSIGNED NOT NULL auto_increment primary key,
year INT(4) UNSIGNED DEFAULT NULL,
seasonID INT(10) UNSIGNED NOT NULL,
INDEX semesterID_key (semesterID),
INDEX seasonID_key (seasonID),
FOREIGN KEY (seasonID) REFERENCES Season(seasonID)
ON DELETE CASCADE ON UPDATE CASCADE
)TYPE=InnoDB;
DROP TABLE IF EXISTS Section;
CREATE TABLE Section
(
sectionID INT(10) UNSIGNED NOT NULL auto_increment primary key,
courseID INT(10) UNSIGNED NOT NULL,
sectionNumber INT(4) UNSIGNED DEFAULT NULL,
semesterID INT(10) UNSIGNED NOT NULL,
INDEX sectionID_key (sectionID),
INDEX courseID_key (courseID),
INDEX semesterID_key (semesterID),
FOREIGN KEY (courseID) REFERENCES Course(courseID)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (semesterID) REFERENCES Semester(semesterID)
ON DELETE CASCADE ON UPDATE CASCADE
)TYPE=InnoDB;
DROP TABLE IF EXISTS Teaches;
CREATE TABLE Teaches
(
instructorID INT(10) UNSIGNED NOT NULL,
sectionID INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (instructorID,sectionID),
INDEX instructorID_key (instructorID),
INDEX sectionID_key (sectionID),
FOREIGN KEY (instructorID) REFERENCES Instructor(instructorID)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (sectionID) REFERENCES Section(sectionID)
ON DELETE CASCADE ON UPDATE CASCADE
)TYPE=InnoDB;
DROP TABLE IF EXISTS EnrolledIn;
CREATE TABLE EnrolledIn
(
studentID INT(10) UNSIGNED NOT NULL,
sectionID INT(10) UNSIGNED NOT NULL,
grade CHAR(2) DEFAULT NULL,
PRIMARY KEY (studentID,sectionID),
INDEX studentID_key (studentID),
INDEX sectionID_key (sectionID),
FOREIGN KEY (studentID) REFERENCES Student(studentID)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (sectionID) REFERENCES Section(sectionID)
ON DELETE CASCADE ON UPDATE CASCADE
)TYPE=InnoDB;
Sign In or Register to comment.