Scribd
Upload
12 views2 pages

SQL Database for City Locations

The document outlines a series of SQL commands to create and manipulate a database named LocationDB, including creating a table for city locations with coordinates. It demonstrates inserting city data, updating a city's coordinates, and deleting a city entry. Finally, it retrieves and displays the remaining cities with their longitude and latitude values.

Uploaded by

aditigade9999
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
12 views2 pages

SQL Database for City Locations

The document outlines a series of SQL commands to create and manipulate a database named LocationDB, including creating a table for city locations with coordinates. It demonstrates inserting city data, updating a city's coordinates, and deleting a city entry. Finally, it retrieves and displays the remaining cities with their longitude and latitude values.

Uploaded by

aditigade9999
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Assignment No.

9
Query:
CREATE DATABASE LocationDB;

Output:

Query OK, 1 row affected (0.01 sec)

Query:
USE LocationDB;

Output:

Database changed

Query:
CREATE TABLE CityLocations (
id INT AUTO_INCREMENT PRIMARY KEY,
city_name VARCHAR(50),
coordinates POINT);

Output:

Query OK, 0 rows affected (0.02 sec)

Query:
SHOW COLUMNS FROM CityLocations;

Output:

+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| city_name | varchar(50) | YES | | NULL | |
| coordinates | point | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Query:
INSERT INTO CityLocations (city_name, coordinates)
VALUES
('Mumbai', ST_GeomFromText('POINT(72.8777 19.0760)')),
('Delhi', ST_GeomFromText('POINT(77.1025 28.7041)')),
('Chennai', ST_GeomFromText('POINT(80.2707 13.0827)')),
('Kolkata', ST_GeomFromText('POINT(88.3639 22.5726)'));

Output:
Records: 4 Duplicates: 0 Warnings: 0

Query:
SELECT id, city_name, ST_AsText(coordinates) AS Location FROM CityLocations;

Output:
+----+-----------+----------------------------+
| id | city_name | Location |
+----+-----------+----------------------------+
| 1 | Mumbai | POINT(72.8777 19.0760) |
| 2 | Delhi | POINT(77.1025 28.7041) |
| 3 | Chennai | POINT(80.2707 13.0827) |
| 4 | Kolkata | POINT(88.3639 22.5726) |
+----+-----------+----------------------------+
4 rows in set (0.00 sec)

Query:
UPDATE CityLocations
SET coordinates = ST_GeomFromText('POINT(72.8777 19.0000)')
WHERE city_name = 'Mumbai';

Output:

Query OK, 1 row affected (0.01 sec)


Rows matched: 1 Changed: 1 Warnings: 0

Query:
DELETE FROM CityLocations WHERE city_name = 'Chennai';

Output:

Query OK, 1 row affected (0.00 sec)

Query:
SELECT city_name, ST_X(coordinates) AS Longitude, ST_Y(coordinates) AS Latitude FROM
CityLocations;

Output:

+-----------+-----------+-----------+
| city_name | Longitude | Latitude |
+-----------+-----------+-----------+
| Mumbai | 72.8777 | 19.0000 |
| Delhi | 77.1025 | 28.7041 |
| Kolkata | 88.3639 | 22.5726 |

+-----------+-----------+-----------+
3 rows in set (0.00 sec)

You might also like