Loading...
nikbikbank avatar nikbikbank Trainer 76 Точки

Решения от подготовка за изпит 19 октомври

Здравейте,

Решенията от днес.

-- 1 --

-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               10.1.25-MariaDB - mariadb.org binary distribution
-- Server OS:                    Win32
-- HeidiSQL Version:             9.4.0.5125
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- Dumping structure for table the_nerd_herd.chats
CREATE TABLE IF NOT EXISTS `chats` (
  `id` int(11) NOT NULL,
  `title` varchar(32) NOT NULL,
  `start_date` date NOT NULL,
  `is_active` bit(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Dumping data for table the_nerd_herd.chats: ~0 rows (approximately)
/*!40000 ALTER TABLE `chats` DISABLE KEYS */;
/*!40000 ALTER TABLE `chats` ENABLE KEYS */;

-- Dumping structure for table the_nerd_herd.credentials
CREATE TABLE IF NOT EXISTS `credentials` (
  `id` int(11) NOT NULL,
  `email` varchar(30) NOT NULL,
  `password` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Dumping data for table the_nerd_herd.credentials: ~0 rows (approximately)
/*!40000 ALTER TABLE `credentials` DISABLE KEYS */;
/*!40000 ALTER TABLE `credentials` ENABLE KEYS */;

-- Dumping structure for table the_nerd_herd.locations
CREATE TABLE IF NOT EXISTS `locations` (
  `id` int(11) NOT NULL,
  `latitude` float NOT NULL,
  `longitude` float NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Dumping data for table the_nerd_herd.locations: ~0 rows (approximately)
/*!40000 ALTER TABLE `locations` DISABLE KEYS */;
/*!40000 ALTER TABLE `locations` ENABLE KEYS */;

-- Dumping structure for table the_nerd_herd.messages
CREATE TABLE IF NOT EXISTS `messages` (
  `id` int(11) NOT NULL,
  `content` varchar(200) NOT NULL,
  `sent_on` date NOT NULL,
  `chat_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_messages_chats` (`chat_id`),
  KEY `FK_messages_users` (`user_id`),
  CONSTRAINT `FK_messages_chats` FOREIGN KEY (`chat_id`) REFERENCES `chats` (`id`),
  CONSTRAINT `FK_messages_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Dumping data for table the_nerd_herd.messages: ~0 rows (approximately)
/*!40000 ALTER TABLE `messages` DISABLE KEYS */;
/*!40000 ALTER TABLE `messages` ENABLE KEYS */;

-- Dumping structure for table the_nerd_herd.users
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL,
  `nickname` varchar(25) NOT NULL,
  `gender` char(1) NOT NULL,
  `age` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  `credential_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `credential_id` (`credential_id`),
  KEY `FK_users_locations` (`location_id`),
  CONSTRAINT `FK_users_credentials` FOREIGN KEY (`credential_id`) REFERENCES `credentials` (`id`),
  CONSTRAINT `FK_users_locations` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Dumping data for table the_nerd_herd.users: ~0 rows (approximately)
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
/*!40000 ALTER TABLE `users` ENABLE KEYS */;

-- Dumping structure for table the_nerd_herd.users_chats
CREATE TABLE IF NOT EXISTS `users_chats` (
  `user_id` int(11) NOT NULL,
  `chat_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`,`chat_id`),
  UNIQUE KEY `user_id_chat_id` (`user_id`,`chat_id`),
  KEY `FK_users_chats_chats` (`chat_id`),
  CONSTRAINT `FK_users_chats_chats` FOREIGN KEY (`chat_id`) REFERENCES `chats` (`id`),
  CONSTRAINT `FK_users_chats_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Dumping data for table the_nerd_herd.users_chats: ~0 rows (approximately)
/*!40000 ALTER TABLE `users_chats` DISABLE KEYS */;
/*!40000 ALTER TABLE `users_chats` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

-- 2 -- 

INSERT INTO `messages` (`content`, `sent_on`, `chat_id`, `user_id`)	
SELECT
	CONCAT(`u`.`age`, '-', `u`.`gender`, '-', `l`.`latitude`, '-', `l`.`longitude`) AS `content`,
	'2016-12-15' AS `sent_on`,
	(CASE 
	WHEN `u`.`gender` = 'M' THEN ROUND(POW(`u`.`age` / 18, 3), 0)
	WHEN `u`.`gender` = 'F' THEN CEIL(SQRT(`u`.`age` * 2))
	END) AS `chat_id`,
	`u`.`id` AS `user_id`
	FROM `users` AS `u`
	JOIN `locations` AS `l` ON `u`.`location_id` = `l`.`id`
	WHERE `u`.`id` >= 10 AND `u`.`id` <= 20;


-- 3 --

UPDATE `chats` AS `c`
JOIN `messages` AS `m` ON `m`.`chat_id` = `c`.`id` AND `sent_on` < `start_date`
SET `c`.`start_date` = `m`.`sent_on`

-- 4 --

DELETE `l`
	FROM `locations` AS `l`
	LEFT JOIN `users` AS `u` ON `u`.`location_id` = `l`.`id`
WHERE `u`.`id` IS NULL;

-- 5 --

SELECT `nickname`, `gender`, `age` 
FROM `users`
WHERE `age` >= 22 AND `age` <= 37
ORDER BY `id`;

-- 6 --

SELECT `content`, `sent_on`
FROM `messages`
WHERE `sent_on` > '2014-05-12' AND `content` LIKE '%just%'
ORDER BY `id` DESC

-- 7 --

SELECT `title`, `is_active`
FROM `chats`
WHERE (`is_active` = 0 AND CHAR_LENGTH(`title`) < 5)
	OR SUBSTRING(`title`, 3, 2) = 'tl'
ORDER BY `title` DESC;

-- 8 -- 

SELECT `c`.`id`, `title`, `m`.`id`
FROM `chats` AS `c`
JOIN `messages` AS `m` ON `m`.`chat_id` = `c`.`id`
WHERE `m`.`sent_on` < '2012-03-26' AND SUBSTRING(`c`.`title`, -1, 1) = 'x'
ORDER BY `c`.`id`, `m`.`id`

-- 9 --

SELECT `c`.`id`, COUNT(`m`.`id`) AS `total_messages`
FROM `chats` AS `c`
JOIN `messages` AS `m` ON `m`.`chat_id` = `c`.`id` AND `m`.`id` < 90
GROUP BY `c`.`id`
ORDER BY `total_messages` DESC, `c`.`id`
LIMIT 5;	

-- 10 --

SELECT `nickname`, `email`, `password`
FROM `users` AS `u`
JOIN `credentials` AS `c` ON `u`.`credential_id` = `c`.`id`
WHERE `email` LIKE '%co.uk'
ORDER BY `email`

-- 11 --

SELECT `id`, `nickname`, `age`
FROM `users` AS `u`
WHERE `location_id` IS NULL
ORDER BY `id`

-- 12 --

SELECT `m`.`id`, `m`.`chat_id`, `m`.`user_id`
FROM `messages` AS `m`
LEFT JOIN `users_chats` AS `uc` 
	ON `uc`.`user_id` = `m`.`user_id` AND `uc`.`chat_id` = `m`.`chat_id`
WHERE `uc`.`user_id` IS NULL AND `m`.`chat_id` = 17
ORDER BY `m`.`id` DESC

-- 13 --

SELECT `nickname`, `c`.`title`, `latitude`, `longitude`
FROM `users` AS `u`
JOIN `locations` AS `l` ON `l`.`id` = `u`.`location_id`
JOIN `users_chats` AS `uc` ON `uc`.`user_id` = `u`.`id`
JOIN `chats` AS `c` ON `c`.`id` = `uc`.`chat_id`
WHERE `latitude` >= 41.139999 AND `latitude` <= 44.129999
	AND `longitude` BETWEEN 22.209999 AND 28.359999
	
ORDER BY `c`.`title`

-- 14 --

SELECT `title`, `m`.`content`
FROM `chats` AS `c`
LEFT JOIN `messages` AS `m` ON `c`.`id` = `m`.`chat_id`
WHERE `c`.`start_date` = (SELECT `start_date` 
								  FROM `chats`
								  ORDER BY `start_date` DESC 
								  LIMIT 1)  
ORDER BY `m`.`sent_on`, `m`.`id`

-- 15 --

DROP FUNCTION IF EXISTS udf_get_radians;        
CREATE FUNCTION udf_get_radians(`degrees` FLOAT)
RETURNS FLOAT                                   
BEGIN                                           
	return RADIANS(`degrees`);                   
END

-- 16 --

CREATE PROCEDURE udp_change_password(`user_email` VARCHAR(30), `new_password` VARCHAR(20))
BEGIN
	IF	(SELECT `id` FROM `credentials` WHERE `email` = `user_email`) IS NULL THEN
		SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'The email does\'t exist!';
	ELSE 
		UPDATE `credentials` SET `password` = `new_password` WHERE `email` = `user_email`;
	END IF;
	
END

-- 17 --

CREATE PROCEDURE udp_send_message(`u_id` INT, `c_id` INT, `chat_msg` VARCHAR(200))
BEGIN
	IF	(SELECT `user_id` FROM `users_chats` 
		 WHERE `user_id` = `u_id` AND `chat_id` = `c_id`) IS NULL THEN
		SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'There is no chat with that user!';
	ELSE 
		INSERT INTO `messages` (`content`, `sent_on`, `chat_id`, `user_id`)
		VALUES (`chat_msg`, '2016-12-15', `c_id`, `u_id`);
	END IF;
	
END

-- 18 --

CREATE TRIGGER del_msg
AFTER DELETE 
ON `messages`
FOR EACH ROW
BEGIN
	INSERT INTO `messages_log` VALUES (OLD.id, OLD.content, OLD.sent_on, OLD.chat_id, OLD.user_id);
END

-- 19 --

CREATE TRIGGER del_urs_before
BEFORE DELETE 
ON `users`
FOR EACH ROW
BEGIN
	DELETE FROM `messages` WHERE `user_id` = OLD.id;
	DELETE FROM `messages_log` WHERE `user_id` = OLD.id;	
	DELETE FROM `users_chats` WHERE `user_id` = OLD.id;
	
END

 

Тагове:
0
Databases Basics - MySQL
DMinchev91 avatar DMinchev91 4 Точки

Добре де, трета задача не трябва ли първо да намери кое sent_on е с най-ранна дата ( защото чатовете имат по няколко sent_on) и тогава да сетне start_date на него . Това предполгам с вложена заявка може да стане ?

0
21/10/2017 20:26:06
nikbikbank avatar nikbikbank Trainer 76 Точки

Не ни интересува кое съобщение е с най-малка дата, а съобщенията, които са преди началото на чатовето. Можем да намерим тази дата чрез вложена завка и да си структурираме решенето по малко по-различен начин. Би трябвало да се получи вярно отново.

0
Можем ли да използваме бисквитки?
Ние използваме бисквитки и подобни технологии, за да предоставим нашите услуги. Можете да се съгласите с всички или част от тях.
Назад
Функционални
Използваме бисквитки и подобни технологии, за да предоставим нашите услуги. Използваме „сесийни“ бисквитки, за да Ви идентифицираме временно. Те се пазят само по време на активната употреба на услугите ни. След излизане от приложението, затваряне на браузъра или мобилното устройство, данните се трият. Използваме бисквитки, за да предоставим опцията „Запомни Ме“, която Ви позволява да използвате нашите услуги без да предоставяте потребителско име и парола. Допълнително е възможно да използваме бисквитки за да съхраняваме различни малки настройки, като избор на езика, позиции на менюта и персонализирано съдържание. Използваме бисквитки и за измерване на маркетинговите ни усилия.
Рекламни
Използваме бисквитки, за да измерваме маркетинг ефективността ни, броене на посещения, както и за проследяването дали дадено електронно писмо е било отворено.