Lab3_Solution

pdf

School

University of Toronto *

*We aren’t endorsed by this school

Course

350

Subject

Computer Science

Date

Dec 6, 2023

Type

pdf

Pages

Uploaded by MagistrateWorldChinchilla30

MIE 350 Lab 3 Answers 1. Accessing database and run SQL queries from client using the web interface. d. 2. The search functionality is made available over REST using the Student REST Controller. a. Please find the relevant function in the REST controller (controller/StudentController) and identify the URL, the HTTP method and the inputs. @GetMapping ( "/students/search/{searchstring}" ) List<Student> searchStudent ( @PathVariable ( "searchstring" ) String searchString){ URL: /students/search/{searchstring} The HTTP method: GET The inputs: searchstring b. Use the Insomnia client (instruction in lab 2) to run a search for the term “Lannister”. How many students matched the search? What are their names? In Insomnia client, send a GET request to: http://localhost:8085/students/search/Lannister Three students have matched the search: Tyrion Lannister, Cersei Lannister, & Jaime Lannister.

3. Please extend CMS as follows: a. Extend the entity Student to have another field for initials (e.g., “R.”). This should not be a mandatory field (i.e., do not use decorator @NotEmpty, and instead use @Nullable). Note that as Student extends Person, it would be more appropriate to add the initials field to the Person entity such that it would apply to all entities that extend Person (i.e., also the Professor entity). In ‘Person’ class: @Nullable private String initials ; b. Edit the data.sql file such that at least some of the students in the database would have initials. INSERT INTO students (id, firstName, lastName, initials, email) VALUES ( 1111 , 'Tyrion' , 'Lannister' , 'T.L.' , 'tyrion.lannister@mail.univ.ca' ); INSERT INTO students (id, firstName, lastName, initials, email) VALUES ( 2222 , 'Cersei' , 'Lannister' , 'C.L.' , 'cersei.lannister@mail.univ.ca' ); INSERT INTO students (id, firstName, lastName, initials, email) VALUES ( 3333 , 'Jaime' , 'Lannister' , NULL , 'jaime.lannister@mail.univ.ca' ); INSERT INTO students (id, firstName, lastName, email) VALUES ( 4444 , 'Daenerys' , 'Targaryen' , 'jaime.targaryen@mail.univ.ca' ); INSERT INTO students (id, firstName, lastName, email) VALUES ( 5555 , 'Jon' , 'Snow' , 'jon.snow@mail.univ.ca' ); c. Extend the search function in the student repository such that it also supports searching in the initials field.

@Query (value = "select * from students s " + "where lower(s.firstName) like lower(concat('%', :searchTerm, '%')) " + "or lower(s.lastName) like lower(concat('%', :searchTerm, '%'))" + "or lower(s.initials) like lower(concat('%', :searchTerm, '%'))" , nativeQuery = true ) List<Student> search ( @Param ( "searchTerm" ) String searchTerm); d. Finally, use Insomnia to make sure you are able to search students based on initials. 4. Homework – further SQL practice (not graded, but important to complete at home as preparation for project and lab quizzes): In the database web interface, please run the following queries: (if you need to refresh your SQL knowledge https://www.w3schools.com/sql/ ) a. List the names (first and last) of all professors that are teaching more than two courses.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

b. List the names of all students together with their average grade. c. Calculate the average grade for each course.

Related Documents

CMS-Test_Plan_Submission-v1.docx

homework assignment 1.pdf

ExampleQuiz.pdf

Lab2_Solution.pdf

Lab7.pdf

CIS 1130 Topic 7 Labs.docx

Exam1A_Solutions.pdf

Quiz 11 Solutions.pdf

Recommended textbooks for you

C++ for Engineers and Scientists

Computer Science

ISBN:9781133187844

Author:Bronson, Gary J.

Publisher:Course Technology Ptr

A Guide to SQL

Computer Science

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Course Technology Ptr

Np Ms Office 365/Excel 2016 I Ntermed

Computer Science

ISBN:9781337508841

Author:Carey

Publisher:Cengage

Systems Architecture

Computer Science

ISBN:9781305080195

Author:Stephen D. Burd

Publisher:Cengage Learning

C++ Programming: From Problem Analysis to Program...

Computer Science

ISBN:9781337102087

Author:D. S. Malik

Publisher:Cengage Learning

Programming Logic & Design Comprehensive

Computer Science

ISBN:9781337669405

Author:FARRELL

Publisher:Cengage

SEE MORE TEXTBOOKS

Recommended textbooks for you

C++ for Engineers and Scientists
Computer Science
ISBN:9781133187844
Author:Bronson, Gary J.
Publisher:Course Technology Ptr
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Systems Architecture
Computer Science
ISBN:9781305080195
Author:Stephen D. Burd
Publisher:Cengage Learning
C++ Programming: From Problem Analysis to Program...
Computer Science
ISBN:9781337102087
Author:D. S. Malik
Publisher:Cengage Learning
Programming Logic & Design Comprehensive
Computer Science
ISBN:9781337669405
Author:FARRELL
Publisher:Cengage