You are not logged in.

#1 2021-09-23 13:35:26

cloudytechi147
New Member
Registered: 2021-09-23
Posts: 2
Website

Problem with query in "Database System Concepts 7th ed" by Silberschat

I'm experiencing difficulty with learning data sets directly at the primary lap.

On page 104 of Silberschatz, he presents this question while clarifying subqueries.

select dept_name, avg_salary
from (select dept_name, avg (salary)
      from instructor
      group by dept_name)
      as dept_avg (dept_name, avg_salary)
where avg_salary > 42000;

However, this code doesn't work in my system, which I later learned is actually MariaDB rather than MySQL. I'm also using Adminer. The error is as follows

Error in query (1064): Syntax error near '(dept_name, avg_salary) where avg_salary > 42000' at line 5

I'm using BunsenLabs Lithium, which doesn't support MySQL or phpMyAdmin, so I can't switch without reinstalling my whole system.

I've run this query through two syntax validators.

This one https://www.eversql.com/sql-syntax-check-validator/ said the query was fine, but poorly optimized.

This one https://www.piliapp.com/mysql-syntax-check/ Gave the same invalid syntax error as my system.

However, Silberschatz made a browser-based SQL command-line based on SQLite to query the same database that the book uses. I ran his query as it was written in the book into the command line https://www.db-book.com/db7/university- … sqljs.html, which throws a similar error

Uncaught Error: near "(": syntax error

MySQL/MariaDB version : mysql Ver 15.1 Distrib 10.3.23-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Offline

#2 2021-09-23 15:37:34

rbh
Moderator
From: Sweden/Vasterbotten/Rusfors
Registered: 2016-08-11
Posts: 1,230

Re: Problem with query in "Database System Concepts 7th ed" by Silberschat

cloudytechi147 wrote:

I'm using BunsenLabs Lithium, which doesn't support MySQL or phpMyAdmin, so I can't switch without reinstalling my whole system.

Bunsenlab is fully compatible with Debian. If you have Debian 10, you have to enable backports to get phpMyAdmin. You can migrate to Debian 11 and install phpMyAdmin from standard repo.

See https://packages.debian.org/search?suit … phpMyAdmin

Is not sql-lang the same on both MariaDB and MySQL...?

You can install deb binaries for MySQL from their repository. See https://www.itzgeek.com/how-tos/linux/d … an-10.html


// Regards rbh

Please read before requesting help: Guide to getting help,
Introduction to the Bunsenlabs Lithium Desktop and other help topics under "Help Resources" on the BunsenLabs menu

Offline

#3 2021-09-23 18:06:17

twoion
一期一会
Registered: 2015-08-10
Posts: 3,354

Re: Problem with query in "Database System Concepts 7th ed" by Silberschat

Yes, MySQL and MariaDB have diverged since their split.

Have you considered using docker / a mysql server container to get any number of RDBMS running? This is the way to go when you as a developer need random things in specific versions. Install docker and docker-compose, then, in a directory, create the following file docker-compose.yaml:

version: "3"
volumes:
        mysql-data:
services:
        mysql:
                image: mysql
                container_name: mysql
                restart: unless-stopped
                ports:
                        - 3306:3306
                environment:
                        - "MYSQL_ROOT_PASSWORD=password"
                volumes:
                        - mysql-data:/var/lib/mysql:rw

With docker running, run sudo docker-compose up -d in that directory. It'll create a mysql container, and its port 3306 will be exposed at 127.0.0.1:3306. Connect to that port using the mysql command line client or other tooling to execute against the containerized mysql server. Adjust the mysql version you're running by specifying an image tag, see https://hub.docker.com/_/mysql for available versions.

Offline

#4 2021-09-23 18:30:03

rbh
Moderator
From: Sweden/Vasterbotten/Rusfors
Registered: 2016-08-11
Posts: 1,230

Re: Problem with query in "Database System Concepts 7th ed" by Silberschat

Has not Silberschatz's written the cource to be used with whatever SQL-server you have? Be it Microsoft SQL, MySQL or MariaDB? I can not find that he expressly writes that you should install MySQL...


// Regards rbh

Please read before requesting help: Guide to getting help,
Introduction to the Bunsenlabs Lithium Desktop and other help topics under "Help Resources" on the BunsenLabs menu

Offline

Board footer

Powered by FluxBB