ITC573 Data and Knowledge Engineering

Introduction

SQLite is a software library that implements a self-contained, having no server and does not require any configuration to execute SQL query (Bhosale et al. 2015). It means that after installation, there is no need to do any configuration in the system for SQLite software. The database in SQLite engine can be linked statically or dynamically as per requirement (Tuan, Cheon and Won 2016).

The report includes the solution to the assignment. It also has the screenshot attached to each solution.  

Discussion

Checkpoint 23-25

Answer1

SELECT P.name,

       P.phone

FROM   publishers P

       JOIN bookjobs BJ

         ON P.cust_id = BJ.cust_id

WHERE  BJ.jobtype = 'R'

ORDER  BY name;

Answer 2

SELECT *

FROM   items

WHERE  price < (SELECT Avg(price)

                FROM   items);

Answer 3

SELECT *

FROM   pos p

WHERE  po_date = (SELECT Min(po_date)

                  FROM   pos

                  WHERE  vendor_id = p.vendor_id)

ORDER  BY vendor_id;

Checkpoint 26-29

Answer 1

SELECT *

FROM   po_items

WHERE  quantity >

       (

              SELECT Min(quantity)

              FROM   po_items

              WHERE  job_id= '002' )

AND    job_id IS NOT '002' ;

Answer 2

SELECT P.job_id,

       P.po_id,

       P.po_date,

       P.vendor_id

FROM   pos P

       JOIN bookjobs BJ

         ON P.job_id = BJ.job_id

       JOIN publishers Pu

         ON BJ.cust_id = Pu.cust_id

WHERE  Pu.cust_id IN (SELECT Pu.cust_id

                      FROM   publishers Pu

                      WHERE  Pu.creditcode = 'C');

Answer 3

SELECT *

FROM   publishers

WHERE  cust_id NOT IN (SELECT cust_id

                       FROM   bookjobs

                       WHERE  job_id IN (SELECT job_id

                                         FROM   pos

                                         WHERE  vendor_id = 'ABC'));

Answer 4

SELECT *

FROM   po_items

WHERE  job_id IN (SELECT job_id

                  FROM   pos

                  WHERE  vendor_id = 'ABC')

       AND po_id IN (SELECT po_id

                     FROM   pos

                     WHERE  vendor_id = 'ABC')

       AND item_id IN (SELECT item_id

                       FROM   items

                       WHERE  price > 50);

Conclusion

Thus, all the Question have been done using the given criteria. That is, the use of In, Subquery, Join, Where clause,  etc. All the codes provided work correctly and the screenshot has been attached to show the result of each code.

References

Bhosale, S.T., Patil, M.T. and Patil, M.P., 2015. Sqlite: Light database system. Int. J. Comput. Sci. Mob. Comput, 44(4), pp.882-885.

Tuan, D.Q., Cheon, S. and Won, Y., 2016, May. On the io characteristics of the sqlite transactions. In Proceedings of the International Conference on Mobile Software Engineering and Systems (pp. 214-224).

Are you stuck with an overly convoluted assignment based on a particular subject matter? Are you looking for a dedicated team of subject matter experts to help you through the hurdle? Take a look here. MyAssignmenthelp.co.uk is right here to back you up with the following services and beyond. 

• Finance assignment help
• Law assignment help
• Accounting assignment help
• Management assignment help
• Biotechnology assignment help 
• Engineering assignment help and more 

So, get in touch with us right away, place your order and have the best SME by your side to provide you with impeccable assignment help online.